Conditional formatting with icons using a formula

Bob White

Board Regular
Joined
May 4, 2008
Messages
61
I want to use the 3 flag icon set as a conditonal format based on a formalus relating to a value in another cell in my spreadsheet.

I am struggling with the formula.

I want a green flag in cell H1 if the value in cell H1 is in the range of .9 to1.1 times the value in cell A1.

If the value in H1 is zero I want a yellow flag in H1

If the value in H1 is neither green of yellow I want a red flag in H1

:confused:
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
According to the help file you can only use Icon Sets with rules that evaluate to a number. If I understand your question you need to apply a rule that evaluates to True/False:
=AND(H1>0.9*$A$1,H1<1.1*$A$1)

You can use this rule (and similar for the other conditions) and format the cell/font in red/yellow/green but cannot use the flags.

Matt
Useful as a short piece of string
 
Upvote 0
Bob

My understanding of the icon sets is also that you cannot use them directly as you want. However, if you really want the flags and not just coloured cells as suggested by Matt, you could investigate a work-around something like this.

1. Use the fomula shown in G1 and copy it down.

2. Apply the flag icon set to the column G cells.

3. Select the column G cells then Conditional Formatting| Manage Rules...|select the rule|Edit Rule...|Show icon only|OK|Apply|OK

I can't show the flags with my screen shot so I have manually coloured the column G cells with the colour of the flag that appears after following the above steps.

Excel Workbook
ABCDEFGH
11 1.05
223
330
443.98
553
Flags
 
Upvote 0
Nice workaround Peter.

Bob, notice that the difference in referencing column A depending on whether you are referring to a fixed value in A1 ($A$1) or just the value in columnA for each row (A1).

Matt
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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