Conditional Format

[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

 

[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

 

[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.