Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I format a cell based on the contents of a different cell?
I have a column of values that can have one of four possible values in each
cell - let's say Red, Amber, Yellow or Green. If the cell has the text "red" in it, I would like to set a fill color of red in the cell immediately adjacent to the cell containing the actual word "red". I'm having a heck of a time finding a way to do this. Anyone have any ideas? Thanks, Z~ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I format a cell based on the contents of a different cell?
Assuming you have a maximum of four possible colors, and ONLY four possible
labels, then conditional formatting should work. Let's say column A will hold the shaded cells, and column B holds the words "Red", "Amber", "Yellow" or "Green". First, since conditional formatting only allows three conditions, you must set all the cells of interest in Column A to a default color. Let's set them all to green to take care of the "Green" label. Now, for each of the other colors, set a conditional format that is like the following: Cell A2 Select "Format/Conditional Formatting" from the menu In the drop-down box, select "Formula Is" In the formula box, type "=IF(B2="Red",1,0)=1" Click on the "Format..." button and set the fill color to red Now, click on the "Add..." button and do two more conditional formats for "Amber" and "Yellow", using the same formula as above. Finally, select cell A2, copy it to the clipboard, select all the other cells of interest in column A, select "Paste Special..." and paste the format from cell A2 into all those other cells. Then see if it works by typing "Red", "Amber", "Yellow", "Green" into random cells in column B. HTH, Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I format a cell based on the contents of a different cell?
egun;221637 Wrote: Assuming you have a maximum of four possible colors, and ONLY four possible labels, then conditional formatting should work. Let's say column A will hold the shaded cells, and column B holds the words "Red", "Amber", "Yellow" or "Green". First, since conditional formatting only allows three conditions, you must set all the cells of interest in Column A to a default color. Let's set them all to green to take care of the "Green" label. Now, for each of the other colors, set a conditional format that is like the following: Cell A2 Select "Format/Conditional Formatting" from the menu In the drop-down box, select "Formula Is" In the formula box, type "=IF(B2="Red",1,0)=1" Click on the "Format..." button and set the fill color to red Now, click on the "Add..." button and do two more conditional formats for "Amber" and "Yellow", using the same formula as above. Finally, select cell A2, copy it to the clipboard, select all the other cells of interest in column A, select "Paste Special..." and paste the format from cell A2 into all those other cells. Then see if it works by typing "Red", "Amber", "Yellow", "Green" into random cells in column B. HTH, Eric Eric, is the IF part necessary, I think B2="Red" will be enough ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61066 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I format a cell based on the contents of a different ce
You could be right, but this is how I have always (succesfully) done it. It
won't hurt to experiment with your suggestion! Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing contents of cell based on Cell Format | Excel Worksheet Functions | |||
How do I format a cell based on the contents of a different cell? | Excel Worksheet Functions | |||
How do I format a cell based on the contents of a different cell? | Excel Discussion (Misc queries) | |||
How do I format one cell based on contents in another cell in Exce | Excel Discussion (Misc queries) | |||
Can I format a row based on the contents of one cell? | Excel Discussion (Misc queries) |