CODEWITHSUNDEEP
vba
user930679
user930679

Reputation: 43

How to do conditional formating an Excel VBA macro?

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

Upvotes: 0

Views: 12327

Answers (2)

DeanOC
DeanOC

Reputation: 7262

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.

Upvotes: 2

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

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".

Upvotes: 2

Related Questions