Any cell in C12:G12 or all the cells in c12:g12.

Since you weren't using a formula--just a comparison:
Cell Value Is equal to ="FAIL" format pattern RED

In either case, maybe you can add one more condition to your format|condional
formatting formulas:

=and(counta(c12:g12)=5,h22="fail")
or
=and(counta(c12:g12)0,h22="fail")

Same kind of thing with Pass.


"Pheasant Plucker®" wrote:

Hello again Dave,

Don't know if you are still reading this thread...sorry for the delay in
replying - I have only got around to working with this spreadsheet again as
I have a teleconference booked for Monday where I need to present some stuff
including this spreadsheet. :-/

Shall I put your name in the credits? ;^)

OK...I did input your suggestions into the Conditional Formatting in Cell
H20 and it does work exactly as you stated - many thanks.

Incidentally I also lowered the pass mark from 80 to 75

The one problem I have now is Cell H22 that is setup as follows;

At 'rest' this will display nothing but a YELLOW pattern - no text.
When below the H20 pass mark of 75 it displays FAIL on a RED pattern
When equal to or above the H20 pass mark of 75 it displays PASS on a GREEN
pattern.

What I really need is for it not to change its default behaviour of
displaying no text on a yellow background until those cells C12:G12 have
been completed - much as we deferred H20 to do nothing until C12:G12 have
all been completed and then display as above (either FAIL on a RED pattern
if H20 is under 75 or PASS on a GREEN pattern if H20 is 75 or over).

Does that make sense?

If it helps H22 currently has the following attributes;

Formula:
=IF(H20=0,"",IF(H20<75,"FAIL","PASS"))

Conditional Formatting:
Cell Value Is equal to ="'FAIL" format pattern RED
Cell Value Is equal to ="'PASS" format pattern GREEN

Your (or any other:-) help would be much appreciated!

Thanks & kind regards,
-=pp=-

"Dave Peterson" wrote in message
...
Maybe you could post exactly what you did.

select your cell and format|Conditional formatting

Then copy both formulas into your reply.

"Pheasant Plucker®" wrote:

Whoops...sorry Dave.

Shows my ignorance I guess..:-/

I did try the CF that you suggested and the cell doesn't turn red
immediately when you input the first number in the first cell which was
what
I wanted.

However it stays yellow all the time now!

It doesn't turn green when above 80 nor red below 80 even after those 5
cells have data in - it just stays yellow...

No worries...probably I missed something else fundamental...

Thanks for trying.

Kind regards,
-=pp=-

"Dave Peterson" wrote in message
...
I didn't mean for you to change the formula in the cell. I meant for
you
to
change the conditional formatting formula/rules.

Since you're using "Cell value is", you'll have to change it to
"Formula
is"

First format:
=and(counta(c12:g12)=5,and(1<=h20,h20<80))

And second format:
=and(counta(c12:g12)=5,h20=80)



"Pheasant Plucker®" wrote:

Hi Dave,

Thanks for your quick reply.

The existing formula in H20 is [=H17-H19]

Conditional Formatting for H20 is two-fold;
Cell Value between 1 - 79.99 Format pattern Red
Cell Value greater than or equal to Format 80 pattern Green

I did try what you suggested by prefixing the existing formula to
give;
[=AND(COUNTA(C12:G12)=5,H17-H19)) but then the word FALSE or TRUE
appears in
cell H20 which I do not want.

Nice try - thanks anyway :-)

Kind regards,
-=pp=-

"Dave Peterson" wrote in message
...
Depending on what you used for your conditional formatting rules,
maybe
you
could just add another requirement:

=and(counta(c12:g12)=5,(yourexistingrules))



"Pheasant Plucker®" wrote:

Does the subject even make sense?

I have a cell H20 that when the spreadsheet is blank is coloured
yellow
to
highlight the fact that it has a special purpose.

When the spreadsheet is blank the number 0 is displayed in this
cell.

It has Conditional Formatting applied to it to turn the cell red
and
display
the word FAIL if the summed result is between 1 and 79.99

For 80 and above the cell is coloured green and the word PASS is
displayed
in it.

All great so far...

However the summed result in this cell H20 depends upon the
result
of
another cell H17 that in turn depends upon the input of 5 other
cells
C12:G12

I would rather H20 does not change colour until all of those 5
cells
C12:G12
have data in them as it would be wrong to display FAIL in a red
cell
until
the result of all 5 cells are input.

Does that make sense and is this even possible?

--
Regards,
-pp-

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson