Conditional Formatting using Excel VBA code

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 WithEnd Sub

Note: this is tested in Excel 2010.

Edit: Updated code based on comments.

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 sheetWS_PROMO.Select' Disable EventsApplication.EnableEvents = False' Delete all conditional formatting rules in sheetCells.FormatConditions.Delete' CREATE ALL THE CONDITIONAL FORMATTING RULES:' (1) Make negative values redWith Cells(1, 1).FormatConditions.add(xlCellValue, xlLess, "=0")    .Font.Color = -16776961    .StopIfTrue = FalseEnd With' (2) Highlight defined good margin as green valuesWith Cells(1, 1).FormatConditions.add(xlCellValue, xlGreater, "=CP_HIGH_MARGIN_DEFINITION")    .Font.Color = -16744448    .StopIfTrue = FalseEnd With' (3) Make article strategy "D" redWith Cells(1, 1).FormatConditions.add(xlCellValue, xlEqual, "=""D""")    .Font.Bold = True    .Font.Color = -16776961    .StopIfTrue = FalseEnd With' (4) Make article strategy "A" blueWith Cells(1, 1).FormatConditions.add(xlCellValue, xlEqual, "=""A""")    .Font.Bold = True    .Font.Color = -10092544    .StopIfTrue = FalseEnd With' (5) Make article strategy "W" greenWith Cells(1, 1).FormatConditions.add(xlCellValue, xlEqual, "=""W""")    .Font.Bold = True    .Font.Color = -16744448    .StopIfTrue = FalseEnd With' (6) Show special cost in bold green fontWith Cells(1, 1).FormatConditions.add(xlCellValue, xlNotEqual, "=0")    .Font.Bold = True    .Font.Color = -16744448    .StopIfTrue = FalseEnd 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 = FalseEnd With' (8) Make heading rows bold with yellow backgroundWith Cells(1, 1).FormatConditions.add(Type:=xlExpression, Formula1:="=IF($B8=""H"";TRUE;FALSE)")    .Font.Bold = True    .Interior.Color = 13434879    .StopIfTrue = FalseEnd With' Modify the "Applies To" rangesCells.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 = FalseEnd Sub