Extend conditional formatting with formula

[guatelize]

How can I extend this recorded macro including the formula with more than 3 conditions :

Sub Button1_Click()
Range("A11:W500").FormatConditions.Delete
Range("A11:W500").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEFT($A11;5)=""Grand"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
Selection.FormatConditions(1).Interior.ColorIndex = 35

End With

Selection.FormatConditions(1).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RIGHT($A11;5)=""Total"""
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
End With
With Selection.FormatConditions(2).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(2).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
Selection.FormatConditions(2).Interior.ColorIndex = 36
End With

Range("B11:W500").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RIGHT($B11;5)=""Total"""
With Selection.FormatConditions(3).Font
.Bold = True
.Italic = False
End With
With Selection.FormatConditions(3).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(3).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
Selection.FormatConditions(3).Interior.ColorIndex = 37
Range("S1").Select
End With
End Sub


Thanks for your pro help

[Bob Phillips]

You cannot have more than 3 CF condition pre-Excel 2007, you would need event code to monitor the cells, but you can tidy up that code



Sub Button1_Click()
With Range("A11:W500")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEFT($A11,5)=""Grand"""
With .FormatConditions(1)
With .Font
.Bold = True
.Italic = False
End With
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Interior.ColorIndex = 35
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RIGHT($A11,5)=""Total"""
With .FormatConditions(2)
With .Font
.Bold = True
.Italic = False
End With
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Interior.ColorIndex = 36
End With
End With

With Range("B11:W500")
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RIGHT($B11,5)=""Total"""
With .FormatConditions(3)
With .Font
.Bold = True
.Italic = False
End With
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Interior.ColorIndex = 37
End With
End With
End Sub