How to parameterize the cell (ex: L3) in a VBA macro to add conditional formatting

0
 

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
   
Answer
Discuss

Answers

0

Hello Paul,

Your problem is caused by a syntax error. You need to remove Cnt from within the quotes. Also, I don't think you need to use IF statements. You also don't need the ".PatternColorIndex = xlAutomatic" and ".TintAndShade = 0" lines. These lines are only required/important if you are using something other than the default settings. These can be deleted or commented out.

Try the following:

> for green cell fill, replace your formula with: "=AND(L" & Cnt & ">0, L" & Cnt & "<3001)"       -- Lx is > 0 and < 3001 [0 to 3000]

> for yellow cell fill replace your formula with:  "=AND(L" & Cnt & ">3000, L" & Cnt & "<5001)"       -- Lx is > 3000 and < 5001 [3001 to 5000]

> for red cell fill replace your formula with: "=L" & Cnt & ">5001"       -- Lx is > 5000 [5001 and above]

If you want to keep the IF statements then remove Cnt from within the quotes as per my examples.

Cheers   ;-}

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login