Hi All
I am applying some Conditional formatting by VBA to both the same selection and different selections.
As most of the code is the same every time, I was hoping to be able to shorten the existing code:
Presumably I could use variables for the code that changes. Setting a range variable for each range but I'm not sure how I would do this.
Any help greatly appreciated.
I am applying some Conditional formatting by VBA to both the same selection and different selections.
As most of the code is the same every time, I was hoping to be able to shorten the existing code:
Code:
Sub Status_CF()
'Status = U
Range("I2:I1048576").Select
Range("I2").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$I2=""U"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = rgbRed
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("F2").Select
'Status = N
Range("I2:I1048576").Select
Range("I2").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$I2=""N"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = rgbRed
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("F2").Select
'Not Found
Range("I2:I1048576").Select
Range("I2").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$I2=""Not Found"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 130, 0)
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("F2").Select
End Sub
Presumably I could use variables for the code that changes. Setting a range variable for each range but I'm not sure how I would do this.
Any help greatly appreciated.