Cell color formula
 

I would like a formula to change colors in cells B1-BX based on the values of
cells A1-AX, which are percentages from 0% to 100%. So, if A5=100%, B5 would
be absolute Red. If A6=99%, B6 would be just a little less red and if
A94=0%, B94 would be white.
Thanks
Lafayette, LA

Cell color formula
 

Select your entire range from B1 down to B# and then use Format | Conditional
Format from the menu.
For the first condition choose:
Formula Is and then type in =AND(A10,A1<1)
and set your format for the lighter shade of red you want.

Then choose [Add] to begin a second condition and again choose Formula Is
and for the formula portion, type this in
=A1=1
and again choose [Format...] to set bright red for the cell shading.

You don't need one for 0, since the default shade is unshaded/white
appearing. But if you do have them shaded something other than
uncolored/white, then use [Add] to set a 3rd condition. Once more you need
Formula Is and this time the formula will be
=A1=0
and set format to white shading.

Excel will adjust the addresses in each of the B# cells automatically for
you.

"Scafidel" wrote:

I would like a formula to change colors in cells B1-BX based on the values of
cells A1-AX, which are percentages from 0% to 100%. So, if A5=100%, B5 would
be absolute Red. If A6=99%, B6 would be just a little less red and if
A94=0%, B94 would be white.
Thanks
Lafayette, LA

Cell color formula
 

Thanks for quick response. I am only getting two colors, though. 100% is
red as I wanted, but anything less is the same light pink (the color I copied
out of Paint).
Lafayette, LA


"JLatham" wrote:

Select your entire range from B1 down to B# and then use Format | Conditional
Format from the menu.
For the first condition choose:
Formula Is and then type in =AND(A10,A1<1)
and set your format for the lighter shade of red you want.

Then choose [Add] to begin a second condition and again choose Formula Is
and for the formula portion, type this in
=A1=1
and again choose [Format...] to set bright red for the cell shading.

You don't need one for 0, since the default shade is unshaded/white
appearing. But if you do have them shaded something other than
uncolored/white, then use [Add] to set a 3rd condition. Once more you need
Formula Is and this time the formula will be
=A1=0
and set format to white shading.

Excel will adjust the addresses in each of the B# cells automatically for
you.

"Scafidel" wrote:

I would like a formula to change colors in cells B1-BX based on the values of
cells A1-AX, which are percentages from 0% to 100%. So, if A5=100%, B5 would
be absolute Red. If A6=99%, B6 would be just a little less red and if
A94=0%, B94 would be white.
Thanks
Lafayette, LA

Cell color formula
 

Basically that's what the formulas I gave you do:
#1: anything greater than 0 and less than 100 = pink
#2: anything 100% = bright red
optional #3 (or default) would be 0 = white.

If you need 100 different shades of Red, there are only 2 ways to attack it:
In Excel 2007 you may have pretty much unlimited conditional formatting
(limited by memory) - but you'll need a pretty sharp eye to distinguish 100
shades of red to set up the formulas?

In any previous version of Excel you are limited to a max of 3 conditional
formats and to get 100 options, you would have to resort to Visual Basic.

So, is what you really want 100 different shades of red/colors for 100
different values in A#; 1 for each percentage point from 1 to 100, plus white
for 0%?


"Scafidel" wrote:

Thanks for quick response. I am only getting two colors, though. 100% is
red as I wanted, but anything less is the same light pink (the color I copied
out of Paint).
Lafayette, LA


"JLatham" wrote:

Select your entire range from B1 down to B# and then use Format | Conditional
Format from the menu.
For the first condition choose:
Formula Is and then type in =AND(A10,A1<1)
and set your format for the lighter shade of red you want.

Then choose [Add] to begin a second condition and again choose Formula Is
and for the formula portion, type this in
=A1=1
and again choose [Format...] to set bright red for the cell shading.

You don't need one for 0, since the default shade is unshaded/white
appearing. But if you do have them shaded something other than
uncolored/white, then use [Add] to set a 3rd condition. Once more you need
Formula Is and this time the formula will be
=A1=0
and set format to white shading.

Excel will adjust the addresses in each of the B# cells automatically for
you.

"Scafidel" wrote:

I would like a formula to change colors in cells B1-BX based on the values of
cells A1-AX, which are percentages from 0% to 100%. So, if A5=100%, B5 would
be absolute Red. If A6=99%, B6 would be just a little less red and if
A94=0%, B94 would be white.
Thanks
Lafayette, LA

Cell color formula
 

I should also mention that in pre-2007 versions of Excel you are limited to
56 colors in a workbook. In 2007 you do have availability to " 16 million
colors (32bit with full access to 24 bit color spectrum" {from Excel 2007
Help}

You may need to read up on how the human eye detects and percieves color. I
mention this because just recently on one of the 'learning/science' channels
I caught a comment that I though was rather limited: they said the human eye
could only see about 110 different colors. They did not say under what
conditions. And perhaps they were talking about perception in looking at a
particular scene, I don't know. But here's another read on it from a dye
manufacturer:
http://www.pburch.net/dyeing/dyelog/...8/E1447734446/

Two comments are of particular note from that page:
"...How many different colors can the human eye distinguish? The
Encyclopedia Britannica mentions that the human eye can distinguish
wavelengths as close together as 1 nm apart in the blue-green and yellow
areas of the spectrum, but only those 10 or more nanometers apart in the deep
red and violet. If the entire visible spectrum, from 380 to 740 nanometers,
could be distinguished at a resolution of only 1 nanometer, the total number
of spectral colors would be (740-380), or 360; for a resolution of 10
nanometers, it would be one-tenth this, so the total number of spectral
colors is somewhere between 36 and 360. ..."
(note that that refers to spectral colors, not variations in shading of each
based on added quantity of white - JLL)
and
"...The actual estimate for how many different colors the human eye can
distinguish varies between one and ten million, depending on the reference
which you consult. However, the perception of color varies from one person to
another, so there can be no single number that is true for everyone. The
number of different colors that you, as an individual, can distinguish also
varies dramatically according to the conditions; it drops to zero in low
light conditions..."

The big point I note that applies to your request is their comment about the
eye being 1/10 as accurate in distinguishing shades of red/violet as it is in
distinguishing shades of blue-green/yellow.




"Scafidel" wrote:

Thanks for quick response. I am only getting two colors, though. 100% is
red as I wanted, but anything less is the same light pink (the color I copied
out of Paint).
Lafayette, LA


"JLatham" wrote:

Select your entire range from B1 down to B# and then use Format | Conditional
Format from the menu.
For the first condition choose:
Formula Is and then type in =AND(A10,A1<1)
and set your format for the lighter shade of red you want.

Then choose [Add] to begin a second condition and again choose Formula Is
and for the formula portion, type this in
=A1=1
and again choose [Format...] to set bright red for the cell shading.

You don't need one for 0, since the default shade is unshaded/white
appearing. But if you do have them shaded something other than
uncolored/white, then use [Add] to set a 3rd condition. Once more you need
Formula Is and this time the formula will be
=A1=0
and set format to white shading.

Excel will adjust the addresses in each of the B# cells automatically for
you.

"Scafidel" wrote:

I would like a formula to change colors in cells B1-BX based on the values of
cells A1-AX, which are percentages from 0% to 100%. So, if A5=100%, B5 would
be absolute Red. If A6=99%, B6 would be just a little less red and if
A94=0%, B94 would be white.
Thanks
Lafayette, LA

Cell color formula
 

I see what you mean. I could use different colors: red from 90-100%, yellow
80-90%, etc. I am familar with VB, but have not used it for colors.
Thanks

"JLatham" wrote:

I should also mention that in pre-2007 versions of Excel you are limited to
56 colors in a workbook. In 2007 you do have availability to " 16 million
colors (32bit with full access to 24 bit color spectrum" {from Excel 2007
Help}

You may need to read up on how the human eye detects and percieves color. I
mention this because just recently on one of the 'learning/science' channels
I caught a comment that I though was rather limited: they said the human eye
could only see about 110 different colors. They did not say under what
conditions. And perhaps they were talking about perception in looking at a
particular scene, I don't know. But here's another read on it from a dye
manufacturer:
http://www.pburch.net/dyeing/dyelog/...8/E1447734446/

Two comments are of particular note from that page:
"...How many different colors can the human eye distinguish? The
Encyclopedia Britannica mentions that the human eye can distinguish
wavelengths as close together as 1 nm apart in the blue-green and yellow
areas of the spectrum, but only those 10 or more nanometers apart in the deep
red and violet. If the entire visible spectrum, from 380 to 740 nanometers,
could be distinguished at a resolution of only 1 nanometer, the total number
of spectral colors would be (740-380), or 360; for a resolution of 10
nanometers, it would be one-tenth this, so the total number of spectral
colors is somewhere between 36 and 360. ..."
(note that that refers to spectral colors, not variations in shading of each
based on added quantity of white - JLL)
and
"...The actual estimate for how many different colors the human eye can
distinguish varies between one and ten million, depending on the reference
which you consult. However, the perception of color varies from one person to
another, so there can be no single number that is true for everyone. The
number of different colors that you, as an individual, can distinguish also
varies dramatically according to the conditions; it drops to zero in low
light conditions..."

The big point I note that applies to your request is their comment about the
eye being 1/10 as accurate in distinguishing shades of red/violet as it is in
distinguishing shades of blue-green/yellow.




"Scafidel" wrote:

Thanks for quick response. I am only getting two colors, though. 100% is
red as I wanted, but anything less is the same light pink (the color I copied
out of Paint).
Lafayette, LA


"JLatham" wrote:

Select your entire range from B1 down to B# and then use Format | Conditional
Format from the menu.
For the first condition choose:
Formula Is and then type in =AND(A10,A1<1)
and set your format for the lighter shade of red you want.

Then choose [Add] to begin a second condition and again choose Formula Is
and for the formula portion, type this in
=A1=1
and again choose [Format...] to set bright red for the cell shading.

You don't need one for 0, since the default shade is unshaded/white
appearing. But if you do have them shaded something other than
uncolored/white, then use [Add] to set a 3rd condition. Once more you need
Formula Is and this time the formula will be
=A1=0
and set format to white shading.

Excel will adjust the addresses in each of the B# cells automatically for
you.

"Scafidel" wrote:

I would like a formula to change colors in cells B1-BX based on the values of
cells A1-AX, which are percentages from 0% to 100%. So, if A5=100%, B5 would
be absolute Red. If A6=99%, B6 would be just a little less red and if
A94=0%, B94 would be white.
Thanks
Lafayette, LA