Conditional Format multiple rows with colour

[MattExcelLearner]

Good morning all

I'm trying to get a conditional format to work on a column for multiple rows.

Basically if all the rows say Closed a column will highlight green with the word passed. If one of the rows has the word open in it the column will go amber and have the word fail.

Is this possible at all?

I have tried to attached an example.

Thanks in advance

[Special-K]

By "If one of the rows has the word open" you really mean "if AT LEAST one of the rows has the word open" ?

in C19
=IF(COUNTIF(C2:C16,"Closed")=15,"Passed","Failed")

Select C2:C16

Conditional Formatting
New Rule
Use a formula to determine...

2 colours required so 2 formulas needed

=C$19="Passed"
format as green

=C$19="Failed"
format as amber

[MattExcelLearner]

Thanks Special-K


This worked a charm:

in C19
=IF(COUNTIF(C2:C16,"Closed")=15,"Passed","Failed")

However, with the colouring which formula should I use for the text to make the colours work, sorry new to this.

Many thanks

[Special-K]

Did you try the Conditional Formatting instructions as suggested?
Just follow the instructions as given.

You want the column highlighted from C2:C16, either green or amber, yes?
"if all the rows say Closed a column will highlight green"
The Conditional Formatting formula to turn the column green/amber are in the instructions above.

[MattExcelLearner]

I did but got in a muddle. (Easily done)

So yes the c19 box that now states pass or fail depending on the above to go green for pass or amber for fail I'm trying to do.

So I selected C2:C16 then clicked on conditional formatting and then new rule.

From there loads of options came up and which one I wasn't sure which to use to put the 2 new formulas in to add colour

=C$19="Passed"
format as green

=C$19="Failed"
format as amber

If that makes any sense?

[Special-K]

So what do you want to green/amber?

JUST C19 or the C2:C16 range?

Because you said "a column will highlight green".
C19 is a cell. If you just want that cell to highlight green then you should say "cell" not "column"

If you just want C19 to change green/amber then just select C19 in my instructions, not C2:C16

[MattExcelLearner]

Yeah just the c19 cell, the whole columns with Open or Closed sounds even trickier.

The attached is what is happening to me.

Thanks

[Special-K]

Thats not what I said, is it.

You've selected "Format cells that only contain"
I said use the option "Use a formula to determine..." which is at the bottom of that list.

Do that and it will work.

Or change your exsiting method so it reads

Cell value equal to Failed

Same for the other formula only change it to Passed

[MattExcelLearner]

Sorry I didn't realise, I'm still finding my way around.

Using the Determine it worked

Sweet, actually achieved something in Excel today.

Many thanks for all your help and understanding in helping me sort this, much appreciated

Until another Excel issue I have, all the best.