Excel VBA Conditional Formatting formula within formula

1
votes

I am stuck and I don't know if there are any solutions to this as I have tried many and kept failing.

Sample

I am trying to add a conditional formatting so cells in column B turns yellow if it is equal or less than 19% of it's partner cell in column A. So in the example above, cell B1 should turn yellow since $19,000 is less than or equal to 19% of $100,000.

I need to add this conditional formatting through excel vba. I tried adding the vba code below but the conditional formatting formula for all of the cells in B1:B3 get's stuck with $A1*0.19. I need B1 conditional formatting formula to be $A1*0.19, then B2 conditional formatting formula would be $A2*0.19 so on and so fort. I have about 350 rows by the way not just 3. Even so, my vba code becomes $A521325*0.19 or something way off the real or actual.

With Sheet1.Range(Sheet1.Cells(1, 2), Sheet1.Cells(3, 2))
    daformula = "=$A1*0.19"
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:=daformula
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent2
        .FormatConditions(1).Interior.TintAndShade = -0.249946592608417
    .FormatConditions(1).StopIfTrue = False
End With

The idea is after running the macro which adds the conditional formatting to the sheet, when the user changes one of the cells in column B the color either disappear or reappear depending on the value the user changed the cell into (the conditional formatting must still work)

3
vbaexcel
I've tested your above code and it works great. Works fine HERE – bp_
No, I'm afraid it doesn't. With that file in your link, try removing all the conditional formatting then run the code again. You will see that 200 of 38 is not turning red and so are the others. It's messed up. This seem hard indeed :s – Jay
Not sure what to tell you, the code is correct for Excel 2010. I suppose I am not much help if you have a different version :( – bp_
Yes I have Excel 2007 :s – Jay

3 Answers

2
votes

Try,

With ActiveSheet.Cells(1, 2).Resize(3, 1)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$B1<=($A1*0.19)"
    .FormatConditions(.FormatConditions.Count).Interior.Color = 65535
End With

Whether you add other details like .SetFirstPriority or .StopIfTrue would depend somewhat on how other CF rules may affect the same cells.

2
votes

You could create an event that monitors any change in column A (place this code in the Sheet1 object)

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then

        'run code to add conditional formatting
        condFormat Target

    End If

End Sub

Now we just have to change your above code to accept the Target and only add formatting for the equivalent cell in Column B

Public sub condFormat (Target as range)
    With target.offset(0,1)
        daformula = "=" & target.address & "*0.19"
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
                              Formula1:=daformula
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent2
        .FormatConditions(1).Interior.TintAndShade = -0.249946592608417
        .FormatConditions(1).StopIfTrue = False
    End With
end sub

I haven't accounted for changing more than 1 cell at once, but this will solve your challenge

0
votes

When creating the conditional formatting from VBA, the cell selection relative to conditional formatting formula is critical.

Try using ActiveSheet.cells(1,1).select if the conditional formatting formula only accesses values on the same row, or ActiveSheet.cells(2,1) if it references the value of the row above.