Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Formatting Cell Based on Conditionally Formatted Cell Color

Posted on 2015-02-09
Medium Priority
77 Views
I'm trying to format cell D3 in the attached example, based on cells in the worksheet 1. The logic is simple, if cells 1!F6:N7 are all green Overview!D3 is green, if any cell in 1!F6N7 is yellow, Overview!D3 is yellow, and if any cell in 1!F6N7 is red, Overview!D3 is red.

I've looked at xlDynamic but can't copy/paste the code, and Pearson's solution but couldn't get anything other the 0 back.

Any help is appreciated!

MV
Color-Formatting-Example.xls
0
Question by:Michael Vasilevsky
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 10
• 8

LVL 33

Expert Comment

ID: 40600362
What criteria would generate the Yellow or Red conditions on worksheet 1?

You could set a formula on Overview that counts the number of occasions where those conditions are met and base the conditional format on that cell. If no Yellow or Red conditions are met, default would be Green.

Thanks
Rob H
0

LVL 10

Author Comment

ID: 40600724
I hope/wish/pray I could use the criteria: if any cell in 1!F6:N7 is yellow, Overview!D3 is yellow, and if any cell in 1!F6:N7 is red, Overview!D3 is red.

Any way to do that with formulas or VBA?

Again, Pearson's solution looked promising but I couldn't get it to work...
0

LVL 33

Expert Comment

ID: 40600822
That is what I am suggesting, rather than counting those that are red or yellow, count those that match the criteria that would make them red or yellow.

Looking at the file you have numerous Conditional formats some of which could be surplus or could be simplified.

I think you would be able to do a count of each of the ranges for cells that match the Red or Yellow conditions; the count might be a bit complex but certainly doable.
0

LVL 10

Author Comment

ID: 40600896
So is it not possible to format based on color? I know I can format based on the criteria in the other worksheet, but that is a workaround rather than the solution I'm looking for isn't it.
0

LVL 33

Expert Comment

ID: 40601077
Not that I am aware of. You would be counting colour so why not count condition instead?
0

LVL 10

Author Comment

ID: 40601169
The advantage would be I wouldn't have to worry about the criteria that created the color in Overview if I can just count color. If I have to count conditions that means I have to maintain what those conditions mean (red, yellow, green) in both places: on Overview and on the other worksheet. amiright?
0

LVL 33

Accepted Solution

Rob Henson earned 2000 total points
ID: 40601665
No, quite the opposite. If you are counting colour and decide to change the colour of the condition, you will have to change the colour count.

If counting occurrences of words/phrases and count is linked to cell containing word/phrase, if the phrase changes the count will update.

I am away from PC at the minute, if I get chance later I will take another look at file.
0

LVL 10

Author Comment

ID: 40601677
Ok, well a moot point if I can't format based on another cells color anyway. The attached file shows the solution I came up with in case anyone is interested.
0

LVL 10

Author Comment

ID: 40601682
0

LVL 10

Author Closing Comment

ID: 40601684
0

LVL 33

Expert Comment

ID: 40601783
This is very lengthy:

=IF('Per Run Eval - #1'!G6="R",TRUE,IF('Per Run Eval - #1'!I6="R",TRUE, IF('Per Run Eval - #1'!K6="R",TRUE, IF('Per Run Eval - #1'!M6="R",TRUE,FALSE))))

Can be shortened to:

=OR('Per Run Eval - #1'!G6="R",'Per Run Eval - #1'!I6="R",'Per Run Eval - #1'!K6="R",'Per Run Eval - #1'!M6="R")

A formula within Conditional formatting needs to always give a TRUE or FALSE result but you don't need to specify conditions for TRUE or FALSE within an IF statement. So with above, if any of the specified cells ="R" the OR function will return TRUE and apply the formatting, if none ="R then the result is FALSE and won't apply formatting.
0

LVL 33

Expert Comment

ID: 40601809
You can also combine the formulas from columns G I K & M into one formula:

=IF(OR(F6>=\$L\$19,H6=\$F\$23,J6=\$F\$26,L6=\$F\$30),"R",IF(OR(F6>=\$L\$18,H6=\$F\$22,L6=\$F\$29),"Y","G"))

In my copy of your file I have put that in column X which can be hidden if so required.

Your conditional formatting on Overview sheet can then just refer to column X and you can get rid of G I K & M on Eval sheet.
0

LVL 10

Author Comment

ID: 40601819
Very nice tips - thank you!
0

LVL 33

Expert Comment

ID: 40601851
BTW, I didn't say it couldn't be done by looking at colour just that I was not aware of a way, certainly not using Conditional Formatting anyway; using VBA I am sure it would be possible but that would have its downsides as well.

For example you could probably set a routine that when Overview is selected it adjust the colour of each of the cells requiring it based on the results in the Eval sheet. However, although the run time of such a routine would be minimal it may be enough to be noticeable and the Undo history would also be wiped when the routine is run.
0

LVL 33

Expert Comment

ID: 40601887
Taking it one step further, you can do aw:ay with formula in column X (or column T if you have got rid of the hidden columns between) and CF formula for D3:D10 on overview would be:

Red - =OR('Per Run Eval - #1'!F6>='Per Run Eval - #1'!\$I\$19,'Per Run Eval - #1'!G6='Per Run Eval - #1'!\$F\$23,'Per Run Eval - #1'!H6='Per Run Eval - #1'!\$F\$26,'Per Run Eval - #1'!I6='Per Run Eval - #1'!\$F\$30)

Yellow - =OR('Per Run Eval - #1'!F6>='Per Run Eval - #1'!\$I\$18,'Per Run Eval - #1'!G6='Per Run Eval - #1'!\$F\$22,'Per Run Eval - #1'!I6='Per Run Eval - #1'!\$F\$29)
0

LVL 33

Expert Comment

ID: 40601897
Also I notice that the various sets of CF on the Eval sheet mostly have 3 rules; one for Red, one for Yellow and one for Green. You can get rid of the Green rule and hard format the cell with Green. If the Red and Yellow rules are met they will take precedence over the hard format, if neither Red or Yellow condition are met the hard format will apply.
0

LVL 33

Expert Comment

ID: 40601924
I have attached my amended version of your file.

I have only looked at the Leading performance indicators and not the lagging performance indicators; no doubt you will be able to apply the same logic to those.

Amendments:
Overview
- applied CF based on comment above to cells D3:D10

Eval sheet
- no hidden columns (except one in Lagging)
- deleted all Green CF rules and applied hard formatting Green instead

Hope that gives you enough to carry on with.

Thanks
Rob H

PS I have enjoyed working this as it gave me a challenge as well!!
Color-Formatting-Example-amended.xlsm
0

LVL 10

Author Comment

ID: 40601978
Thanks so much - you've gone above and beyond!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month7 days, 19 hours left to enroll