Conditional formatting (icons) applied to a range using formulas

dave0

New Member
Joined
Feb 23, 2017
Messages
3
Greetings - I'm using Excel 2011 for Mac.


503698d1487891027-conditional-formatting-icons-applied-to-a-range-using-formulas-screen-shot-2017-02-22-at-8.53.04-pm.png



My watered down example consists of tracking "start" and "end" over a period of 7 days. So not including headers/labels there are 2 rows (B2:I3) and 7 columns. The data are all integers. I want to compare the value of B2 to B3 and display a "check" icon in the cell with the greater value and an "x" icon in the other. i'd also like to display the "!" icon if they happen to be equal. When I create a rule and apply it to B2 only, the "check" and 'x" icons display as expected.




My goal is to apply this rule to the entire range (B2:I2) and have the values being compared iterate to the end of the range. Unfortunately, when I apply it to the range, each successive value is compared to the first value tested ($B$3) and thus the icons do not display properly. I also don't see any way to specify that the "!" icon is to be displayed when the values are equal. Am I missing something?


503699d1487891063-conditional-formatting-icons-applied-to-a-range-using-formulas-screen-shot-2017-02-22-at-9.06.18-pm.png

503700d1487891146-conditional-formatting-icons-applied-to-a-range-using-formulas-screen-shot-2017-02-22-at-9.16.04-pm.png



I tried putting =B3 in the formula but get an error saying that relative references are not allowed. I know I can create a separate rule for each cell, but I find it very hard to believe that this would be the only way to go.


And finally, I find the 3 sections of the icon set rule dialog to be a little confusing. For the section relating to the "!" icon, it says


When value is < Formula and


Which formula is it referring to?




Similarly, the section for the "x" icon reads:


When value is < Formula


Which Formula is THIS one referring to?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
You can either use the built-in CF rules - and use whichever rules have icons available to them (Format Cells Based On Their Value is 1 of those), or you can use the Use Formula rule. However the Use Formula rule does not allow the use of icons, only colors
 
Upvote 0
You can either use the built-in CF rules - and use whichever rules have icons available to them (Format Cells Based On Their Value is 1 of those), or you can use the Use Formula rule. However the Use Formula rule does not allow the use of icons, only colors

I can't seem to figure out how to use the built in CF rule you mention to format the cell based on whether its value is > or < another cell's value. Is this possible?
 
Upvote 0
You could use the Format Only Cells That Contain, then use the "between" boxes to set your upper and lower limit
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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
Back
Top