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

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

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