guatelize
09-02-2008, 06:50 AM
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
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