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" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
>> 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
>>
>>