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.

Answer 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.