Conditionally formatting one cell based if a range of cells contains a certain value

Question

Hi Everyone,

I'm trying to conditionally format one cell based a range of cells containing a certain value.

Context: this is a milestone planner designed to make it easy to manage a range of projects. (See photo).

• Under each project is a series of milestones and these have the option of choosing a 'status', mine are Complete, Scheduled, Behind Schedule / At Risk, and Target Date Missed.
• I essentially want to highlight the empty cell (bordered red) next to the project name with a 'status summary' based on the least favourable status displayed in the cells bordered blue.
• For example, if a project has 10 milestones, and 9 of those milestones are Complete but the other 1 is Behind / At Risk, I want this empty cell next to the project name to be formatted a certain colour.

Other info:

• The 4 project status options are part of a drop down list.

I thank you in advance for the help.

S

Answer 1

Hi Sam

Here my approach to find solution to your requirements

1- Create a helper table for VLOOKUPs and Data validation

2- Create helper column "D" with formula (you can hide it if you like)

For helper column

cell D7 =VLOOKUP($C7,$K$6:$L$10,2,FALSE) and copy down

For cell C6 =VLOOKUP(IF(COUNTIF($D$7:$D$13,1)=COUNTA($D$7:$D$13),1,MAX($D$7:$D$13)),$J$7:$L$10,2,FALSE)

Cell C6 will show the name of the task according to the completion of the milestones

Note: I didn't conditional formatted cells as it will make me work extra,

but in your case it will show your CFs

Check picture below that shows all the steps and formulas used to accomplish your requirements

Do let me know if you require any further help on this. Will be glad to help you.

If this answer would be the solution to your question, Please, share your appreciation by marking it as answered. I would be grateful to you as well.

Thanks

Regards

Jeovany CV

Answer 2

Just to be clear:

• If all the milestones are complete, I want C6 to be formatted green
• If they contain any other status (Scheduled, Behind Schedule / At Risk, Target Date Missed), I want it to show their respected colour (Blue, Orange, Red).
• I want the 'worse' of the statuses to take priority over the others in C6.
• effectively from a glance the person managing this should be able to see the status of each projects without having to look too deeply

Thanks so much for your help.

Sam

Answer 3

Hi Andreas,

Thanks for the quick reply! Sorry that I'm only just getting back to you.

This has worked, sort of.

The only issue is that when I change of the values the cell in question (C6) does not change.

See attached.

Answer 4

You have to use a formula, and the formula in a conditional formatting must return TRUE or FALSE.

So the best way to figure out if a formula works is to try it in the sheet first, e.g.

=COUNTIF($A$1:$A$10,"Complete")<>COUNTA($A$1:$A$10)

The COUNTIF returns the number of "Complete" in a range and the COUNTA returns the number of cells in a range. If they are not equal we get a TRUE and so the conditional formatting is applied.

Any questions?

Andreas.