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