It was NG wrap-around. Try this version
With Range(strI_Range).EntireRow
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$K" & ActiveCell.Row & "=""Completed"""
.FormatConditions(1).Interior.ColorIndex = 33
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$K" & ActiveCell.Row & "=""Cancelled"""
.FormatConditions(2).Interior.ColorIndex = 33
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND(COLUMN()=9,$J" & ActiveCell.Row & "<=3)"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
End With
--
__________________________________
HTH
Bob
"JoAnn" <(E-Mail Removed)> wrote in message
news:F071D2B4-E61B-4CC5-88A5-(E-Mail Removed)...
> Thanks for the code, Bob ...
>
> I tried it & it generates a "Compile Error - Syntax Error" on the
> following
> lines of code:
>
> .FormatConditions.Add Type:=xlExpression, _
>> Formula1:="=$K" & ActiveCell.Row &
>
> All of the conditions are appearing red in the VBA editor.
>
> Thanks for you help with this.
>
> --
> JoAnn
>
>
> "Bob Phillips" wrote:
>
>> With Range(strI_Range).EntireRow
>>
>> .FormatConditions.Delete
>>
>> .FormatConditions.Add Type:=xlExpression, _
>> Formula1:="=$K" & ActiveCell.Row &
>> "=""Completed"""
>> .FormatConditions(1).Interior.ColorIndex = 33
>>
>> .FormatConditions.Add Type:=xlExpression, _
>> Formula1:="=$K" & ActiveCell.Row &
>> "=""Cancelled"""
>> .FormatConditions(2).Interior.ColorIndex = 33
>>
>> .FormatConditions.Add Type:=xlExpression, _
>> Formula1:="=AND(COLUMN()=9,$J" &
>> ActiveCell.Row & "<=3)"
>> With .FormatConditions(3).Font
>> .Bold = True
>> .Italic = False
>> .ColorIndex = 3
>> End With
>> End With
>>
>>
>>
>> --
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "JoAnn" <(E-Mail Removed)> wrote in message
>> news:A89CDF8F-F701-44AC-9980-(E-Mail Removed)...
>> > Need help creating the code for adding conditional formatting to cell
>> > ranges .
>> >
>> > For range (Axx:Ryy), I need the following conditional formatting
>> > conditions
>> > created:
>> > 1 - Shade row grey if column "K" value = "Completed"
>> > 2 - Shade row grey if column "K" value = "Cancelled"
>> >
>> > In addition, column "I" needs to have a 3rd condition:
>> > 3 - Make column "I" value red font if condition met (formula =$Jx <
>> > 3)
>> >
>> > I recorded a macro for creating the conditions & got the following code
>> > that
>> > I can copy to cells. Just not sure how to generalize it so it will not
>> > reference cell $K701 below (it needs to reference columns K & J but
>> > apply
>> > to
>> > all rows in the range):
>> >
>> > Range(strI_Range).Select
>> > Selection.FormatConditions.Delete
>> >
>> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
>> > "=$K701 = ""Completed"""
>> > Selection.FormatConditions(1).Interior.ColorIndex = 33
>> >
>> >
>> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
>> > "=$K701 = ""Cancelled"""
>> > Selection.FormatConditions(2).Interior.ColorIndex = 33
>> >
>> > ' 3rd condition in cond fmtg sub for column I only
>> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J701
>> > <
>> > 3"
>> > With Selection.FormatConditions(3).Font
>> > .Bold = True
>> > .Italic = False
>> > .ColorIndex = 3
>> > End With
>> >
>> > If there is a more elegant way to do this, please let me know. Right
>> > now I
>> > have this as 2 subs (one for col I & the other for the other cells in
>> > the
>> > range).
>> >
>> > I also have a Click Event in Sheet 1 that shades a column for different
>> > values. Not sure how to combine them (if that's possible and/or if it
>> > is
>> > worth doing).
>> >
>> > Thanks for your help!
>> >
>>
>>
>>
|
Similar Threads
