Solved Excel change cell color if data in 1 of 2 other cells exist

June 17, 2015 at 08:18:47
Specs: Windows 7
Excel 2010. I have three cells that need to turn a color (Conditional Format) if data exists in any one of the three cells.
Scenario
Cells remain no fill until data is entered into one of the 3 cells.
C6 will turn grey if data is entered in G6 or K6.
G6 will turn grey if data is entered in C6 or K6.
K6 will turn grey if data is entered in G6 or C6.

Data will be text such as the letter I or R or I123

How do I accomplish the color change in 2 of the 3 cells with only one having data?

message edited by Moparkat


See More: Excel change cell color if data in 1 of 2 other cells exist

Report •


✔ Best Answer
June 17, 2015 at 10:02:20
You will need Three Rules to accomplish what you want:

First:
1) Select your cell C6
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=AND(G6<>"",K6<>"")

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

Second:
1) Select your cell G6
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=AND(C6<>"",K6<>"")

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

Third:
1) Select your cell K6
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=AND(G6<>"",C6<>"")

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

See how that works for you.

MIKE

http://www.skeptic.com/



#1
June 17, 2015 at 08:28:48
Are you asking for help or just simply telling us what you need?

I don't see a question in your post.


message edited by DerbyDad03


Report •

#2
June 17, 2015 at 08:53:52
BTW...Your requirements don't seem to be consistent.

First you say: "I have three cells that need to turn a color (Conditional Format) if data exists in any one of the three cells."

That tells me that if data is entered into any one the 3 cells, all 3 cells should be "turn a color".

However, in the breakdown of the individual cells, it sounds like only 2 of the 3 should turn a color.

For example:

"C6 will turn grey if data is entered in G6 or K6."

Should C6 also turn grey if data is entered into C6? According to "I have three cells that need to turn a color (Conditional Format) if data exists in any one of the three cells" it should, but according to "C6 will turn grey if data is entered in G6 or K6" it shouldn't.

Which is it?

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


Report •

#3
June 17, 2015 at 09:34:48
Sorry - I need color to change in the 2 of 3 cells if the 3rd celll has data in it.

message edited by Moparkat


Report •

Related Solutions

#4
June 17, 2015 at 10:02:20
✔ Best Answer
You will need Three Rules to accomplish what you want:

First:
1) Select your cell C6
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=AND(G6<>"",K6<>"")

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

Second:
1) Select your cell G6
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=AND(C6<>"",K6<>"")

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

Third:
1) Select your cell K6
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=AND(G6<>"",C6<>"")

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#5
June 17, 2015 at 11:35:46
I did exactly as wiritten with Cond Format values for the cells but when I enter any sort of data (number or letter) the other cells do not fill with color.

Report •

#6
June 17, 2015 at 11:44:00
Are you sure that we shouldn't be using OR instead of AND?

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


Report •

#7
June 17, 2015 at 11:47:44
I see a formula of IsNotBlank that might be what I am searching for.
What I want effect 3 cells per group.
If one is not blank, I need the other two to color fill.

Seems to be that Conditional Formatting is what I am after but the formula eludes me.


Report •

#8
June 17, 2015 at 11:53:03
Thank you - OR is the one that makes the other cell color fill when data is entered into it.
This is so awesome! Going to impress my boss....

Report •

#9
June 17, 2015 at 13:19:55
Sorry, misread you first post, the formula should be =OR(), as you now know.

MIKE

http://www.skeptic.com/


Report •

Ask Question