Conditional Formatting using Excel VBA code

Question

I have Range object called DistinationRange which contains reference to range B3:H63

I want to apply the following two conditional formatting rules using Excel VBA code dynamically. (Because the range would not be same all the time)

1. If Cell column D is blank, no formatting should be applied (Need to use Stop If True there)
2. If Value in Cell of column E is lesser than value in cell of column F, that whole row should have green background.

I tried a lot using recording but it's not recording properly.

Kindly Help.

Solution 1

This will get you to an answer for your simple case, but can you expand on how you'll know which columns will need to be compared (B and C in this case) and what the initial range (A1:D5 in this case) will be? Then I can try to provide a more complete answer.

Sub setCondFormat()
    Range("B3").Select
    With Range("B3:H63")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($D3="""",FALSE,IF($F3>=$E3,TRUE,FALSE))"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 5287936
                .TintAndShade = 0
            End With
        End With
    End With
End Sub

Note: this is tested in Excel 2010.

Edit: Updated code based on comments.

Solution 2

I think I just discovered a way to apply overlapping conditions in the expected way using VBA. After hours of trying out different approaches I found that what worked was changing the "Applies to" range for the conditional format rule, after every single one was created!

This is my working example:

Sub ResetFormatting()
' ----------------------------------------------------------------------------------------
' Written by..: Julius Getz Mørk
' Purpose.....: If conditional formatting ranges are broken it might cause a huge increase
'               in duplicated formatting rules that in turn will significantly slow down
'               the spreadsheet.
'               This macro is designed to reset all formatting rules to default.
' ---------------------------------------------------------------------------------------- 

On Error GoTo ErrHandler

' Make sure we are positioned in the correct sheet
WS_PROMO.Select

' Disable Events
Application.EnableEvents = False

' Delete all conditional formatting rules in sheet
Cells.FormatConditions.Delete

' CREATE ALL THE CONDITIONAL FORMATTING RULES:

' (1) Make negative values red
With Cells(1, 1).FormatConditions.add(xlCellValue, xlLess, "=0")
    .Font.Color = -16776961
    .StopIfTrue = False
End With

' (2) Highlight defined good margin as green values
With Cells(1, 1).FormatConditions.add(xlCellValue, xlGreater, "=CP_HIGH_MARGIN_DEFINITION")
    .Font.Color = -16744448
    .StopIfTrue = False
End With

' (3) Make article strategy "D" red
With Cells(1, 1).FormatConditions.add(xlCellValue, xlEqual, "=""D""")
    .Font.Bold = True
    .Font.Color = -16776961
    .StopIfTrue = False
End With

' (4) Make article strategy "A" blue
With Cells(1, 1).FormatConditions.add(xlCellValue, xlEqual, "=""A""")
    .Font.Bold = True
    .Font.Color = -10092544
    .StopIfTrue = False
End With

' (5) Make article strategy "W" green
With Cells(1, 1).FormatConditions.add(xlCellValue, xlEqual, "=""W""")
    .Font.Bold = True
    .Font.Color = -16744448
    .StopIfTrue = False
End With

' (6) Show special cost in bold green font
With Cells(1, 1).FormatConditions.add(xlCellValue, xlNotEqual, "=0")
    .Font.Bold = True
    .Font.Color = -16744448
    .StopIfTrue = False
End With

' (7) Highlight duplicate heading names. There can be none.
With Cells(1, 1).FormatConditions.AddUniqueValues
    .DupeUnique = xlDuplicate
    .Font.Color = -16383844
    .Interior.Color = 13551615
    .StopIfTrue = False
End With

' (8) Make heading rows bold with yellow background
With Cells(1, 1).FormatConditions.add(Type:=xlExpression, Formula1:="=IF($B8=""H"";TRUE;FALSE)")
    .Font.Bold = True
    .Interior.Color = 13434879
    .StopIfTrue = False
End With

' Modify the "Applies To" ranges
Cells.FormatConditions(1).ModifyAppliesToRange Range("O8:P507")
Cells.FormatConditions(2).ModifyAppliesToRange Range("O8:O507")
Cells.FormatConditions(3).ModifyAppliesToRange Range("B8:B507")
Cells.FormatConditions(4).ModifyAppliesToRange Range("B8:B507")
Cells.FormatConditions(5).ModifyAppliesToRange Range("B8:B507")
Cells.FormatConditions(6).ModifyAppliesToRange Range("E8:E507")
Cells.FormatConditions(7).ModifyAppliesToRange Range("A7:AE7")
Cells.FormatConditions(8).ModifyAppliesToRange Range("B8:L507")


ErrHandler:
Application.EnableEvents = False

End Sub

Comments

[Sid Holland]

Replacing "A1:D5" with "DestinationRange" would sort out the dynamic nature of the initial range.

[Kevin Pope]

:-) Indeed it would! Although I'm also curious if columns B and C are static or will change along with DestinationRange

[Sid Holland]

If the range is defined first as A1 to D5, then the macro is run, then the range is moved around the worksheet, then it appears to be fine and the formula updates dynamically. But if the range is first defined as something different (L4 to O8, for example) then weird things happen with the formula in the condition. There must be a way to make it properly dynamic.

[TechGeek]

@KevinPope: Actually Range and columns I need to use is static. Thanks for your answer but conditional formats applied is giving incorrect results. Range I need to use is "B3:H62" and I added conditional format as Range("B3:H62").FormatConditions.Add Type:=xlExpression, Formula1:="=IF($D3="""",FALSE,IF($F3>=$E3,TRUE,FALSE))" Also when I look into formatting conditions from Excel's front end interface it is showing as =IF($D2="",FALSE,IF($F2>=$E2,TRUE,FALSE))

[TechGeek]

@SidHolland: You are right. Have you been able to find any solution to this problems?

[Kevin Pope]

The way you've edited the if statements doesn't jive with your original question - you have columns D, E and F in your edit, whereas there were only columns B and C in the original question. Also, what are the incorrect results you're getting?

[TechGeek]

@KevinPope Actually in question I used sample range. In actual, my range is B3:H62. When I go to Excel's interface to edit the cond. formatting, it is showing =IF($D2="",FALSE,IF($F2>=$E2,TRUE,FALSE)) as conditional formatting rule which is not same as set by code. And because of this, conditional formatting is not working as per the requirements. Pls help

[TechGeek]

@KevinPope: Please have a look at excelforum.com/excel-programming-vba-macros/…. You will get the file in which I have used your from there

[Kevin Pope]

Are you deleting rows after creating the conditional formatting rule? That's the only way I can reproduce this issue. If it's not necessary to have the Range restriction in the first line, you can use With Cells instead of With Range("B3:H62") to avoid this. Make sure you delete the old rules first.

[TechGeek]

@KevinPope: I got the solution. Actually first row of Range is 3 so I need to select Cell B3 by Range("B3").Select. After doing that, it's working. You may please update your answer according so that I can mark it as correct. Thanks for your help.

[Kevin Pope]

@Tejas - updated the answer; it's interesting that you need to Select the Range in order for it to work. Well done, I'm glad you got it working!

[TechGeek]

@KevinPope: Yes, you need to be on the row where the Conditional Formatting starts. BTW marked as correct. Thanks.

[TechGeek]

@KevinPope: And just for your reference, I got a better formula like "=AND($D3<>"""",$F3>=$E3)"

[Kevin Pope]

Nice one - that's much cleaner!

[koyae]

Do you really mean to set Application.EnableEvents to False again in the error-handling section, or is that a typo?