Conditional Format Based on Cell Value - vba

[ChrisOK]

With the click of a toolbar button, I've got my vba inserting a new tab, renaming it, inserting headers and now working on the code to insert all the formulas needed in 12 columns to copy down as far as there is data.
The 4th column D is the hardest. Currently, I'm using manual "Conditional Formatting" to colorize the cells in Col D but need the VBA to place that "Conditional Formatting" code into the sheet so that when the sheet is created, it will hold the conditional formatting feature.

Specifically, the formulas I'm using in the Conditional format wizard are:
Formula: =$B2<$D2 then turn the cell green Applies to: =$D$2:D
Formula: =$B2>$D2 then turn the cell red Applies to: =$D$2:D

Copy down (in other words, the colorizing should work all the way down Column D as long as there is data in Col D.

 

[vavs]

Here is what I would do. Two things though. 1 you do not test for a condition where b=d. Second, I am not sure how you would apply this to a new sheet. There would be no data so it becomes a mute point to format. You can add a hot key or even put a button that references the macro. What I did not include was an indication of what worksheet you are working on. This should get you started though.

<code>

Sub Conditional()
Dim LR As Integer
Dim CR As Integer

'Get the last row that contains data
LR = Range("D" & Rows.Count).End(xlUp).Row

CR = 1

Do While CR <= LR


Range("D" & CR).Select
Range("D" & CR).Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B" & CR & ">$D" & CR
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ColorIndex = 3
End With


Range("D" & CR).Select
Range("D" & CR).Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B" & CR & "<$D" & CR
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ColorIndex = 4
End With

CR = CR + 1

Loop
End Sub

</code>

 

[ChrisOK]

I like it! Very nice - Thank you!
I've got other code that sits previous to the Conditional Formatting code which draws in data from other tabs within the same workbook for formulate a Dashboard per se. From those formulated results/values, the Conditional Formatting knows how to act based on the drawn in value of each cell.