Solved

Formatting Cell Based on Conditionally Formatted Cell Color

Posted on 2015-02-09
18
71 Views
Last Modified: 2016-02-11
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
Comment
Question by:Michael Vasilevsky
  • 10
  • 8
18 Comments
 
LVL 31

Expert Comment

by:Rob Henson
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

by:Michael Vasilevsky
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 31

Expert Comment

by:Rob Henson
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

by:Michael Vasilevsky
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 31

Expert Comment

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

Author Comment

by:Michael Vasilevsky
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 31

Accepted Solution

by:
Rob Henson earned 500 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

by:Michael Vasilevsky
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

by:Michael Vasilevsky
ID: 40601682
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 10

Author Closing Comment

by:Michael Vasilevsky
ID: 40601684
Thank you for your help!
0
 
LVL 31

Expert Comment

by:Rob Henson
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 31

Expert Comment

by:Rob Henson
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

by:Michael Vasilevsky
ID: 40601819
Very nice tips - thank you!
0
 
LVL 31

Expert Comment

by:Rob Henson
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 31

Expert Comment

by:Rob Henson
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 31

Expert Comment

by:Rob Henson
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 31

Expert Comment

by:Rob Henson
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

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now