How to do conditional formating an Excel VBA macro?

Question

vba I have used the below code to insert conditional formatting in an excel cell..

range("d" & rowno).Select
Selection.Offset(1, 0).EntireRow.Insert
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="= RC > 7"
Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 65535 'Yellow
    .TintAndShade = 0
End With

The above works fine by comparing the grater than value defined which is "7"... But if i pass variable "lhigh" in which value is stored and the same im passing it in the formaula it does not works. e.g; lhigh=7

range("d" & rowno).Select
Selection.Offset(1, 0).EntireRow.Insert
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="= RC > lhigh"
Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 65535 'Yellow
    .TintAndShade = 0
End With

Please let me know how we can than calculate greater than check if we pass variable instead of direct integer value

Jean-François Corbett · Accepted Answer · 2012-09-07 10:08:07Z

2

You need this:

Formula1:="= RC > " & lhigh

i.e. you need to do a string concatenation using the & operator.

"= RC > " & lhigh will then evaluate as "= RC > 7".

answered Sep 7, 2012 at 10:08

Jean-François Corbett

38.7k30 gold badges144 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DeanOC · Accepted Answer · 2012-09-07 10:30:07Z

2

If I name a cell "Arc" and another to "lhigh" then the following sub works for me in Excel 2007

Sub test()

Dim foo As Range

Set foo = ActiveWorkbook.Sheets("Sheet1").Range("C3")

With foo.FormatConditions _
        .Add(xlExpression, Formula1:="=Arc>lhigh")

    With .Font
        .Bold = True
        .ColorIndex = 4
    End With
End With

End Sub

this will set conditional formatting on cell C3 which will kick in when the value in Arc > lhigh.

Perhaps you should simplify your code to something basic like this and then add the extra complexity. I'm guessing that your problem lies in another part of your code.

answered Sep 7, 2012 at 10:30

DeanOC

7,3326 gold badges44 silver badges57 bronze badges

Collectives™ on Stack Overflow

How to do conditional formating an Excel VBA macro?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related