Conditional Format Syntax Error

[Pam]

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

 

[Rick Rothstein]

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.

 

[Pam]

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