Conditional Formatting Code
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" wrote in message
...
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!
|