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

alee4187

New Member
Joined
Jun 28, 2015
Messages
2
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,506
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?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,767
Members
413,933
Latest member
Msar5586

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top