Conditional formatting | icon sets | relative reference formula


New Member
Aug 7, 2014
In cell A1 I have the number 100 and in cell B1 I have the Green, Yellow, Red icon set so that:

- if the value of cell B1 is greater than A1 the dot is green
- if the value of cell B1 is equal to A1 the dot is yellow
- if the value of cell B1 is less than A1 the dot is red

Applies to = $B$1 and Values of both > and >= is =$A$1

I need to apply this to over 400 rows but cant figure out how to do it as excel barks at me when I try and remove the $ and says "you cannot use relative references in conditional formatting for color scales, data bars, and icon sets" and format painter does not work either. I really don't want to set this up 400 times in a row - Help?


Well-known Member
Mar 31, 2015
Office Version
Well, ya can't do that. When Microsoft fixes this deficiency there will be a national holiday declared.

But don't hold your breath. If you don't mind having the Icon Sets appear in the cell beside your target cell, try this. And when I say 'appear' I mean 'appear to appear' because the Icon Sets cannot be adapted.

In A1 put 100, which is our control value. In B1 through B3 put 120, 100, and 80 respectively (and so on for 400 rows). In C1 through C3, paste =UNCIDOE(11044) and then colour the font some shade of red. Select cells C1 through C3. Create two new CondFrmt rules: 1) =B1=A1 and choose some shade of yellow for the font; 2) =B1>A1 and choose some shade of green for the font.<strike></strike>

This thread ( tells me that the RGB values for the Excels Traffic Lights icons are as follows:
Green: 104,164,144
Amber: 234,194,130
Red: 192,80,77

This reference has all the Unicode characters you could ever wish to look at; this is where I got character 11044, which is a big ole dot.<strike></strike><strike></strike><strike></strike><strike></strike><strike></strike><strike></strike><strike></strike><strike></strike><strike>
Last edited:

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...