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

Report •

#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$3

Can't you just change it to:

=$A$3:$P$3

MIKE

http://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?

MIKE

http://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? ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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...

MIKE

http://www.skeptic.com/


Report •

#8
August 16, 2014 at 13:40:29
Try this,

In the Applies To box in your Conditional Formatting

If it looks like: =$C$3:$P$3

add A3 & B3 to the end

so it looks like: =$C$3:$P$3 , $A$3:$B$3

MIKE

http://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?

MIKE

http://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?

MIKE

http://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 this

With your spreadsheet open,
Click on Format from the menu bar
From the drop down menu, click on Conditional Formatting
From 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 Worksheet

All 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 box

You should currently have something like: =$C$3:$P$3

You want it to look like: =$C$3:$P$3,$A$3:$B$3

Which is your current range selection
A Comma
Your appended range selection

DO 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 Formatting

http://chandoo.org/wp/2009/03/13/ex...

MIKE

http://www.skeptic.com/


Report •

#16
August 18, 2014 at 06:26:54
Click on Format from the menu bar
From the drop down menu, click on Conditional Formatting
From 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...

http://www.excelforum.com/excel-pro...


Report •

#18
August 18, 2014 at 13:28:30
I checked out your link to the excelforum, but you need to be a member to download or view your worksheet.

I take it from the comments that you received,

too many Conditional Formattings to evaluate in the given range for me

and this from one of there forum Gurus, that you have a large variety of
Conditional Formatting going on in your sheet, so it is not just a simple
append this range to that range.

In your reply #10 you posted a one line example of your sheet,
but without knowing what it is your doing, or why, it is difficult
to be of much help.

If you care to explain the, who, what, where, when, how, and why
of your sheet, we might be able to point you in a more productive direction.

MIKE

http://www.skeptic.com/


Report •

Ask Question