Excel VBA Conditional Formatting formula within formula

Question

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)

I've tested your above code and it works great. Works fine HERE — bp_
– bp_, Commented Nov 3, 2014 at 15:43
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
– Jay, Commented Nov 3, 2014 at 20:51
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_
– bp_, Commented Nov 3, 2014 at 20:57

user4039065 · Accepted Answer · 2014-10-31 15:46:34Z

4

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.

answered Oct 31, 2014 at 15:46

user4039065

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

Comments

Galimi · Accepted Answer · 2014-10-31 15:59:29Z

2

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

answered Oct 31, 2014 at 15:59

Galimi

693 bronze badges

1 Comment

Jay Over a year ago

I forgot to say, the entire database is being processed in Excel VBA then later uploaded to Google Docs. Which is why we need CF as CF also get's included when uploading to Google Docs

approxiblue · Accepted Answer · 2015-09-12 01:42:47Z

0

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.

edited Sep 12, 2015 at 1:42

approxiblue

7,16216 gold badges53 silver badges59 bronze badges

answered Sep 12, 2015 at 1:36

Tim G.

11 bronze badge

Collectives™ on Stack Overflow

Excel VBA Conditional Formatting formula within formula

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related