Need Conditional Formatting Help

  • Thread starter Thread starter Jasen
  • Start date Start date
J

Jasen

What I am trying to do is fairly simple...I think. I need to have
"red light, yellow light, green light" format for a spreadsheet I'
working on. The problem is I can get Excel to shade green when th
cell = 100 and yellow when it is from 50 to 99 and red when 0 to 49
but it shades the cells red when there is nothing there. I can'
figure out how to make it so it doesn't shade red when nothing i
present in the cell.

I hope I made this clear!

thanks in advance

~Jase
 
Jasen

try something like: =AND(ISNUMBER($C$19),$C$19>=50,$C$19<100) in the
formula box

Regards

Trevor
 
Jasen

the full RAG set:

=AND(ISNUMBER($C$19),$C$19=100) green

=AND(ISNUMBER($C$19),$C$19>=50,$C$19<100) amber/yellow

=AND(ISNUMBER($C$19),$C$19>=0,$C$19<50) red

Regards

Trevor
 
Try this:

Under Condition 1, put the settings as:
Formula is | =AND(NOT(ISBLANK(B1)),B1>=0,B1<=49)
[Format to shade red, as desired]

Set-up your other 2 conditions under Conditions 2 and 3
 
Jasen,

Seems to me you can just use "Formula is," and for the first condition
(green):
=A1=100

And for the second (yellow):
=A1>=50

And the third (red):
=A1>0

You need to have them in that order. The first one that's TRUE wins,
starting from the top. If you have whole numbers, you don't even have to
use "formula is," you could use "Cell value is."

Change the A1 to match the active (white) cell of your selection.

Also, you may wish to have a separate column for the color, (maybe just a
square or so), to prevent the solid colors from making the data difficult to
read. The conditional formatting can change the formatting of cells other
than the one being examined. Post back for more on that.
 
I've attached an example of what I am trying to do....

as you can see the CF'ing is set for 100 = green, 50-99 = yellow, an
1-49 = red. I want 0-49 but I dont want the empty cells to be shaded.

I tried the above ideas, and I must be doing something wrong becuse al
of the cells turn red....

Thank you all for the suggestions thus far

jase

Attachment filename: example1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=44780
 
Hi
for the third condition change the condition to a formula and enter the
following
=AND(G5<>"",G5>=0,G5<=49)
 
Thank you Frank, it worked like a charm!

Best Regards

Jasen

Thanks to everyone else who responded as well
 
Jasen

with your example sheet, select cells G5 through U7. Select Format |
Conditional Formatting and then, for the three conditions:

Formula is: =AND(ISNUMBER(G5),G5=100)

Formula is: =AND(ISNUMBER(G5),G5>=50,G5<100)

Formula is: =AND(ISNUMBER(G5),G5>=0,G5<50)

the formula will adjust for each cell, whereas my original example related
to a fixed cell. The cell you refer to MUST be the first cell in the range,
in this case G5.

You have a solution though; this is just another way of doing it.

Regards

Trevor
 
Back
Top