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

"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

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

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

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

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

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