Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I format a cell based on the contents of a different cell?
ZoeMcGoe;221586 Wrote: 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? As you have more than 3 conditions, ( except with XL2007), VBA will be necessary -- 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=61084 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I format a cell based on the contents of a different cell?
Suppose your colours (words) are in column D, and you want the cells
in column E to show the appropriate colour. Highlight the cells in column E (for example from E2 to E50), then click on Format | Conditional Formatting. In the pop-up you should choose Formula Is rather than Cell Value Is in the first box, then enter this formula: =D2="Red" Then click on the Format button, choose the Patterns tab and click on Red. Click OK and then Add to set up the second condition. Choose Formula Is again, and enter this formula: =D2="Amber" Then click on the Format button, choose the Patterns tab and click on Amber. Click OK and then Add to set up the third condition. Choose Formula Is again, and enter this formula: =D2="Yellow" Then click on the Format button, choose the Patterns tab and click on Yellow. Click OK twice to exit the dialogue boxes. Finally, with those cells highlighted click on the Fill Colour Icon and choose Green (you can only have 3 CFs with Excel 2003 or earlier) and conditional formatting over-rides normal formatting. Excel will adjust the range references to suit different rows. Hope this helps. Pete On Feb 9, 7:50*pm, ZoeMcGoe wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I format a cell based on the contents of a different cell?
Hi,
The answer is version dependent - in 2003 you will need to VBA write code, in 2007 you can use Conditional Formatting. 2003 is limited to 3 conditions and you have 4, 2007 is limited by available memory. Here is the 2007 solution: Suppose your words are in A1:A1000, 1. select B1:B1000 2. Choose Home, Conditional Formatting, Manage Rules, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =A1="Red" 5. Click the Format button and choose a format. 6. Click OK twice 7. New Rule 8. Choose Use a formula to determine which cell to format 9. In the Format values where this formula is true enter the following formula: =A1="Green" 10. Click the Format button and choose a format. Repeat for each of your 4 words. 11. Click OK tree times. -- If this helps, please click the Yes button Cheers, Shane Devenshire "ZoeMcGoe" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I format a cell based on the contents of a different cell?
You might try goin got Format-Conditional Format. You can either format a
cell based on cell's value, or you can enter a formula resulting in a true/false result to determine how to format. Limits at that pre-2007, you can only use 3 conditions. You "create" the appearance of 4 sometimes by having the default format of cell set to a 4 th condition. Beyond that, you'd have to use VBA to get more conditions. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ZoeMcGoe" wrote: 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I format a cell based on the contents of a different cell?
Threads merged and duplicates deleted -- 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I format a cell based on the contents of a different cell?
What does this mean?
Pete On Feb 9, 8:34*pm, Pecoflyer wrote: Threads merged and duplicates deleted -- 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I format a cell based on the contents of a different cell?
Pete_UK;222199 Wrote: What does this mean? Pete On Feb 9, 8:34*pm, Pecoflyer wrote: Threads merged and duplicates deleted -- 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:'The Code Cage Forums - View Profile: Pecoflyer' (http://www.thecodecage.com/forumz/me...pecoflyer.html) View this thread:'How do I format a cell based on the contents of a different cell? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=61066) Hi Pete, just keeping our forum tidy Cheers -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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) | |||
Set number format based on cell contents | Excel Discussion (Misc queries) | |||
Conditional Format based on contents of cell. | Excel Worksheet Functions |