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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|