Conditional Formatting Code

[JoAnn]

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!

 

[Bob Phillips]

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

 

[JoAnn]

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.

 

[Bob Phillips]

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

 

[JoAnn]

Thanks! This works great.
--
JoAnn

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