Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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