Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

Formatting Cell Based on Conditionally Formatted Cell Color

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
Michael Vasilevsky
Asked:
Michael Vasilevsky
  • 10
  • 8
1 Solution
 
Rob HensonIT & Database AssistantCommented:
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
 
Michael VasilevskySolutions ArchitectAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Michael VasilevskySolutions ArchitectAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
Not that I am aware of. You would be counting colour so why not count condition instead?
0
 
Michael VasilevskySolutions ArchitectAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Michael VasilevskySolutions ArchitectAuthor Commented:
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
 
Michael VasilevskySolutions ArchitectAuthor Commented:
0
 
Michael VasilevskySolutions ArchitectAuthor Commented:
Thank you for your help!
0
 
Rob HensonIT & Database AssistantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Michael VasilevskySolutions ArchitectAuthor Commented:
Very nice tips - thank you!
0
 
Rob HensonIT & Database AssistantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Michael VasilevskySolutions ArchitectAuthor Commented:
Thanks so much - you've gone above and beyond!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now