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         If Cell column D is blank  no formatting should be applied  Need to use Stop If True there    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

User · Accepted Answer

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.

User · Answer

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

[vba] Conditional Formatting using Excel VBA code

Examples related to vba

Examples related to excel

Examples related to conditional-formatting