Conditional Format

S

Scott A

I have several columns that I would like to apply Conditional Formating to
but exclude cells that have $0.00 in them. I would like to use a 3-color
scale from lowest being green, mid being yellow and highest being red. and be
able to copy it down the columns after it is created.

Example:
A B C D E F G H I J K L
M N O P
7 Item1 X X 1.25 X X 0.00 X X 0.00 X X 1.00 X
X 1.38
8 Item2 X X 1.25 X X 0.00 X X 0.00 X X 1.08 X
X 1.50
9 Item3 X X 1.30 X X 0.00 X X 0.00 X X 1.20 X
X 1.33
10 Item4 X X 1.85 X X 0.00 X X 0.00 X X 1.20 X
X 1.50
Etc...

I would like the conditional formating to apply per row and apply the color
as long as it doesn't have a 0.00 in it and rank it from lowest to highest
for cells D7,G7,J7,M7, and P7. Then I would like to copy that setup down the
columns to work the same way. I have about 100 rows as it stands now with
information and formulas already put together and I do not want to mess that
up.

Can anyone help?
Thanks, Scott A
 
P

Pete_UK

So, just to get this clear, your column D contains 1.25, 1.25, 1.30
and 1.85, and you would like the 1.85 to be red, the two 1.25 values
to be green and the one in the middle to be yellow? And you want a
similar format applied to columns G, J, M and P ?

I assume that where you show X in your example you do not want CF to
be applied.

Is that correct?

Pete
 
L

Luke M

Select cells D7, G7, J7, M7, and P7, with D7 being the active cells.
Conditional format, formulas are:

=D7=MIN(IF((MOD(COLUMN($D7:$P7)-1,3)=0)*($D7:$P7<>0),$D7:$P7))

=D7=MEDIAN(IF((MOD(COLUMN($D7:$P7)-1,3)=0)*($D7:$P7<>0),$D7:$P7))

=D7=MAX(IF((MOD(COLUMN($D7:$P7)-1,3)=0)*($D7:$P7<>0),$D7:$P7))

You can then copy these formats down as needed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top