Rick,

Thank you for the prompt reply. That worked.

Do you have any suggestions as to how I can use code to name range and
format each range conditionally for all 9 depts? Maybe something a little
cleaner and condensed than copying the same block eight more times and
changing dept # and format where needed.

I did copy the code for each dept and I get error "1004 - Method 'Range' of
object '_Global failed"
at this line
Range(rFirst, rLast).Name = "Dept4"
because with this instance of the report dumped into Excel there were no
records for Dept4. How can I account for that when it happens?

Thanks again for your help.
Pam

"Rick Rothstein" wrote in message
...
Try it this way...

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<""u"",B2=1)"

Note that quote marks internal to the string constant need to be doubled
up.

--
Rick (MVP - Excel)



"Pam" wrote in message
...
Hi,

I have the following code to name a range supplied by Otto Moehrbach and
I have added code to conditionally format named range but am now
receiving a syntax error at this line:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<"u",B2=1)"


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:="1", After:=rColB(1), LookIn:=xlFormulas,
_
LookAt:=xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious)
Range(rFirst, rLast).Name = "Dept1"

Range("Dept1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<"u",B2=1)"
Selection.FormatConditions(1).Interior.ColorIndex = 4
End Sub

Can someone please tell me what I'm doing wrong? Also, is there a way to
maybe case select this as I have Dept's 1-9 that I need to name and apply
slightly different code to each?
Any help is greatly appreciated.
Thanks in advance,
Pam