Name A Range

  • Thread starter Thread starter Pam
  • Start date Start date
P

Pam

Hi,

I have a spreadsheet with a column for Department that contains numbers. I
would like to be able to sort by Department and then use vba code to name a
range for each department number and then use the range name in conditional
formatting. This is something that I have to do on a weekly basis with a
new list downloaded to Excel, so I would really like to automate as much as
I can. Is this possible?

Thanks in advance for any help.
Pam
 
Pam
What range in each row do you want to name and what do you want to name
it? Otto
 
Otto,

I have columns A-N, but only want to use conditional formatting on Col B
which is Dept. I want to find all 5's listed in column and then name that
range say on this report it will be from B2500:B4749 and then name it Dept5.
Then, if possible, I would like to work this formula into vba so that it
will all run at the same time. I want to use vba so that each week I'm not
typing all the formulas into the spreadsheet for each department.. So I'm
thinking I just want to name the column b range as stated above. Should I
include colC as well - not sure?

=AND(RIGHT(C5432)<>"P",B5432=5) colors column B (Dept 5) for those where
colC does not contain a code ending in "P".

Does this make sense?
Thanks for your help.
Pam
 
Pam
I still don't know what you are doing or what you need. I don't know
what "this formula" is. You say that your data is sorted and that Column B
contains numbers for the departments. This little macro will find the first
and last "5" in Column B and will color that range red and will name that
range "Dept5". I know this isn't exactly what you are looking for but maybe
it's a start. I assumed row 1 has headers and the data starts in row 2.
See what this macro does for you and then tell me more about what you want.
Remember that I know nothing about what you are doing, so be gentle. HTH
Otto
Sub ColorDept()
Dim rColB As Range
Dim rFirst As Range
Dim rLast As Range
Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
Set rFirst = rColB.Find(What:="5", After:=rColB(rColB.Count),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
Set rLast = rColB.Find(What:="5", After:=rColB(1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious)
Range(rFirst, rLast).Interior.ColorIndex = 3
Range(rFirst, rLast).Name = "Dept5"
End Sub
 
Otto,

I appreciate your help in trying to understand what I want to do with this
spreadsheet. I have a weekly report that downloads to Excel. In this report
is a column for Department with numbers 1-9. The report I'm working on now
has over 7,000 rows with each department having its own chunk of the 7,000.
Instead of scrolling down until I find the first 5 in Department 5's section
and click+hold and scroll to the end of Dept5 section and applying my
conditional format to all in Dept 5, I want to automate the process by using
code to name the section and then use the name in a conditional format
formula to apply the condition. When I stated "this formula", I was
referring to the formula listed at the end of the message:
=AND(RIGHT(C5432)<>"P",B5432=5). I want to set code for each dept so I can
click a button and have code run instead of all the scrolling, copying,
pasting for each dept and all the different conditional formats for each.

The code you supplied worked by finding section of Dept 5's, except I don't
need them colored. I will remove this from the code, but can you tell me
how to incorporate my conditional formula =AND(RIGHT(C5432)<>"P",B5432=5)
into this code or perhaps another macro with the formula separate from your
code that I can run from vba. I know a little about vba code in Access and
am trying to learn code in Excel. It has opened up many options as to what
I can do with Excel.

Once again, thanks for your time and help.
Pam
 
Pam
Look at this macro. This macro loops through all of Column B from row 2
to the last occupied row. It finds all the groups of numbers in Column B
and names each group "Dept" and the number in the group (Dept1, Dept2, etc).
It colors nothing. In your formula, what is "5432" or is that just an
example of the row number you want in your formulas? Let's work on the
Conditional Formats and your conditional formulas. First, explain what you
want the conditional format to do for you, in detail. It might help if you
recorded a macro while you setup the Conditional Format and post that macro.
Include details about the conditions you want evaluated, and what formatting
you want to happen when those conditions are met. Otto
Sub NameDept()
Dim rColB As Range
Dim rFirst As Range
Dim rLast As Range
Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
Set rFirst = Range("B2")
Do
Set rLast = rColB.Find(What:=rFirst.Value, After:=rColB(1),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious)
Range(rFirst, rLast).Name = "Dept" & rFirst.Value
Set rFirst = rLast.Offset(1)
Loop Until IsEmpty(rFirst.Value)
End Sub
 
Back
Top