View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
JoAnn JoAnn is offline
external usenet poster
 
Posts: 78
Default Conditional Formatting Code

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!