A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Cell color formula



 
 
Thread Tools Display Modes
  #1  
Old July 22nd, 2007, 12:08 AM posted to microsoft.public.excel.misc
Scafidel
external usenet poster
 
Posts: 35
Default 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
  #2  
Old July 22nd, 2007, 02:14 AM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 3,017
Default 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,A11)
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

  #3  
Old July 22nd, 2007, 04:52 PM posted to microsoft.public.excel.misc
Scafidel
external usenet poster
 
Posts: 35
Default 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,A11)
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

  #4  
Old July 22nd, 2007, 10:24 PM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 3,017
Default 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,A11)
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

  #5  
Old July 22nd, 2007, 10:40 PM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 3,017
Default 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,A11)
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

  #6  
Old July 23rd, 2007, 07:26 PM posted to microsoft.public.excel.misc
Scafidel
external usenet poster
 
Posts: 35
Default 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,A11)
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.