Ive written some VBA code to apply Multiple conditional formats to one column, just to test that it worked I used Colours as the conditional format result to check that the Macro Works properly. However I really need it t apply Number formats as a result instead i.e " @" to the first " @" to the second and so on. And is .Select and Selection. really necessary , how can i reduce some code.
Code:
Sub Cond_Format3()
Application.ScreenUpdating = False
Cells.FormatConditions.Delete
Range("B$8:$B$1500").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(B8)-LEN(SUBSTITUTE(B8,""."",""""))=1 "
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = vbBlue
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B$8:$B$1500").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(B8)-LEN(SUBSTITUTE(B8,""."",""""))=2 "
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = vbMagenta
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B$8:$B$1500").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=ARABIC(MID(B8,FIND("")"",B8,1)+1,100))>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = vbCyan
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B$8:$B$1500").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(FIND("")"",B8,1)=LEN(B8),ISTEXT(C8))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = vbYellow
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B$8:$B$1500").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(FIND("")"",B8,1)=LEN(B8),ISTEXT(C8),LEN(B8)-LEN(SUBSTITUTE(B8,""."",""""))=1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = vbGreen
.TintAndShade = 0
End With
Application.ScreenUpdating = True
End Sub