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


"Otto Moehrbach" wrote in message
...
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


"Pam" wrote in message
...
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

"Otto Moehrbach" wrote in message
...
Pam
What range in each row do you want to name and what do you want to
name it? Otto

"Pam" wrote in message
...
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