# Changing color of cells based on color of other cells

August 16, 2014 at 09:34:52
Specs: Windows XP
 Hello,I have Windows XP, and I need help with a macro to do the following without changing the content of any cell.... Cells C3 - P3 are conditionally formatted. IF any cell C3 through P3 is RED, THEN make cells A3 and B3 RED.Else, IF any cell C3 through P3 is ORANGE, THEN make cells A3 and B3 ORANGE.Else, IF any cell C3 through P3 is YELLOW, THEN make cells A3 and B3 YELLOW.Else, leave cell WHITE.This would be applied to all the other cells in the same column...Thank you for your help.

See More: Changing color of cells based on color of other cells

#1
August 16, 2014 at 11:28:43
 Why can't you just extend the Applies To range in your Conditional Formatting to cover the additional two columns?I would guess that your Applies To box in your Conditional Formatting reads:=\$C\$3:\$P\$3Can't you just change it to:=\$A\$3:\$P\$3MIKEhttp://www.skeptic.com/

Report •

#2
August 16, 2014 at 12:21:36
 No, because cells C-P are dates, while cells A-B are just names.

Report •

#3
August 16, 2014 at 13:03:05
 What is the formula that you are using with the Conditional Formatting?MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
August 16, 2014 at 13:12:43
 Not really sure how to answer that question.... There is no single formula. They are all different. But basically, here is an example...If cell value is between X and Y, display color RED, else if Cell value is between Y and Z, display color ORANGE, else if cell value is between Z and Q, display color yellow. All of these are also using the NOW() function to track the dates of expiration. Second set of cells is just looking for a condition.... If it has "X", highlight RED. If it has "Y", leave as is. Does this help. Again, column A and column B are just names. Column C-P are dates. message edited by MiloG

Report •

#5
August 16, 2014 at 13:14:45
 A and B are just names? Are you implying that names aren't as important as dates? ;-)

Report •

#6
August 16, 2014 at 13:21:23
 Well, the names are constants. They will never change. The dates are variables.

Report •

#7
August 16, 2014 at 13:29:26
 They are all different.Do you mean they reference different Cells or they are each a unique formula?Does each formula have it's own Range of cells?Post a few examples with the cell or range of cells they apply to.I don't completely understand what it is that your doing, so a brief explanation of how your sheet is set up and the purpose would be helpful.If you wish to post an example, please read this HOW-TO for direction on the use of PRE TAGS:http://www.computing.net/howtos/sho...MIKEhttp://www.skeptic.com/

Report •

#8
August 16, 2014 at 13:40:29
 Try this,In the Applies To box in your Conditional FormattingIf it looks like: =\$C\$3:\$P\$3add A3 & B3 to the end so it looks like: =\$C\$3:\$P\$3 , \$A\$3:\$B\$3MIKEhttp://www.skeptic.com/

Report •

#9
August 16, 2014 at 15:55:35
 Attaching a sample did not quite work... message edited by MiloG

Report •

#10
August 16, 2014 at 16:06:02
 ```Prefix Name "Date1" "Date2" "Date3" "Refr. Trng" "Reported" "Lab" "Form 1" "Form 2" "Form 3:" "Form 4" Mr. John Doe 20-Aug-14 07-Jul-15 07-Jul-15 17-Apr-14 Y 27-Sep-14 17-Jul-14 14-Nov-13 X 28-Jul-14```So now, if any of the cells is red, make prefix and name red, else if any of the cells is orange,make prefix and name orange, else if any of the cells is yellow, make prefix and name yellow. message edited by MiloG

Report •

#11
August 16, 2014 at 19:08:04
 Did you try my suggestion to append the two columns to your ranges in the Applies To box?```like: =\$C\$3:\$P\$3 , \$A\$3:\$B\$3 ^ Your Cells comma New Cells ```It is difficult to re-create your sheet with out all the info used to build it.What is the cell range in the Applies To box in your Conditional Formatting?What is the formula that you are using with the Conditional Formatting?How is the Conditional Formatting being applied?MIKEhttp://www.skeptic.com/

Report •

#12
August 16, 2014 at 19:19:19
 That would not work... I am really running out of ideas to describe the issue other than just email you the sample file... A coworker said that I could apply conditional formatting to cells A and B to look for color in cells C-P. This would require the use of triple IF function to search for the definition of colored cell... RED= #FF0000, ORANGE=#FF6600, YELLOW=#FFFF00. Not really sure how the syntax would look like for this.... Would perhaps this be possible?

Report •

#13
August 16, 2014 at 19:29:51
 That would not work.Why not? Have you tried it?What version of Excel are you using?MIKEhttp://www.skeptic.com/

Report •

#14
August 17, 2014 at 04:21:34
 It's a bit older... from 2003. There is no Applies to box.... But even if there was, how would you set hierarchy if all three colors show up in a row?

Report •

#15
August 17, 2014 at 08:13:00
 Try thisWith your spreadsheet open, Click on Format from the menu barFrom the drop down menu, click on Conditional FormattingFrom the drop down menu, click on Manage Rules at the bottom of the menu.The Rules Manager box should appear.In the Show Formatting Rules for: box, select This WorksheetAll the conditional formatting rules for the currently displayed work sheet should appear.There will be an Applies To box next to each rule.Append the two columns A&B to your range in the Applies To boxYou should currently have something like: =\$C\$3:\$P\$3You want it to look like: =\$C\$3:\$P\$3,\$A\$3:\$B\$3Which is your current range selectionA CommaYour appended range selectionDO NOT USE THE ARROW KEYS TO MOVE AROUND IN THE APPLIES TO BOX.Excel will interpret that as selecting a cell and foul things up.If you make a mistake, just use the Backspace key.See how that works.But even if there was, how would you set hierarchy if all three colors show up in a row?That is the function of the RULES which you apply to your range of cells.See here for a good introduction to Conditional Formattinghttp://chandoo.org/wp/2009/03/13/ex...MIKEhttp://www.skeptic.com/

Report •

#16
August 18, 2014 at 06:26:54
 Click on Format from the menu barFrom the drop down menu, click on Conditional FormattingFrom the drop down menu, click on Manage Rules at the bottom of the menu.I dont have this MANAGE RULES option.

Report •

#17
August 18, 2014 at 11:26:09
 If it's any help, I posted the same question on a different forum, soliciting for brainstorming... There, I was able to attach a sample file. If you have time to look at it, it's at this link...

Report •

#18
August 18, 2014 at 13:28:30