Posted By: Anonymous
I have Range object called
DistinationRange which contains reference to range
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.
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.