VBA - How to add conditional formatting with icon depends on the cell value on the next column

[alee4187]

May I know if it is possible to add this by range? For example, for range($E$5:$E$66), I want each of them to compare with the cell value on the next column (say $E$5 to $F$5....$E$66 to $F$66).

I realized I couldn't just do it, so I recorded a Macro and would like to just use a short key on each cell to add this conditional formatting. Please let me know if there is better way to do this.

Yet, the Macro does not work and I don't really know much about VBA. Moreover, when I use 3 or xlEqual in .Operator, I keep getting error. Please help and thank you!!!

Criteria: If the cell>next column, then down red triangle; if the cell=next column, then yellow dash; if the cell<next column, then green up triangle.

Sub Macro()
'
' Macro Macro
'
' Keyboard Shortcut: Ctrl+a
'
CurrentCell = Round(ActiveCell, 2)
Range(ActiveCell.Address).Select
ActiveCell.Value = CurrentCell
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = True
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3Triangles)
End With
With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValuePercent
.Value = Round(ActiveCell.Offset(0, 1), 2)
.Operator = 3
End With
With Selection.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValuePercent
.Value = Round(ActiveCell.Offset(0, 1), 2)
.Operator = 5
End With
End Sub

 

[GlennUK]

Why don't you add an extra column, say G, that does F minus E for the row, and format that area using icons? It took me about 30 seconds to do the whole thing.

I suppose what I'm asking is: why does the icon have to be in column E?