I need help in learning how to parameterize the cell (ex: L3) in a VBA macro formula that adds 3 format conditions to change the fill color depending on the value in the cell. After the code below adds the formulas to the cell, when I inspect the conditional formatting rules in the formulas the "&Cnt" is still showing as &Cnt and has not been transformed to a number of the row, e.g., 3, 4, etc. How do I get that to happen?
Any help is most appreciated!
Thank you.
Paul
NumRows = Cells(Rows.Count, "A").End(xlUp).Row
'Add Conditionall Formating to the response times in Column L (Baseline)
' >0 to 3000 GREEN background
' 3001 to 5000 YELLOW background
' 5001 and above RED background
Range("L3").Select
For Cnt = 3 To NumRows
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(IF(L & Cnt>0,1),IF(L & Cnt<=3000,1),IF(L & Cnt<>0,1))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(IF(L & Cnt>=3001,1),IF(L & Cnt<=5000,1))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=""IF(L & Cnt=5001,1)"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveCell.Offset(1, 0).Select
Next