Cell Color Change, Pass/Fail

[pink_99]

Hi
I am working on a spreadsheet that the final values are in numbers, they should fall in a range so the cells will change color to green if it passes and red if it is failing,

Now at the top to see if the entire sheet has passes how do I tally up all the cells as I cannot say if
=IF(AND(I147="pass",D174="pass"),"pass","Fail")
as it is only the color changes depending on the number.

so how to say pass if all cells are green and fail if not?

Thanks in Advance!!!!

 

[pink_99]

Cell color change pass/fail

Hi
I used conditional formating for color change

 

[NBVC]

We need to know what formula you used to change the cell colour to red/green. Native excel can't count based only on colour. It needs conditions.

 

[pink_99]

color change

in conditional formating

I used if less than cell A1 and greater than Cell B2 , so the number should be in between this 2 values in order to pass.
The cells than change green or red depending on the value entered

 

[NBVC]

Re: cells color change pass/fail

Hi

I guess I am unable to convey myself clearly.
I have attched the page on this link
http://www.geocities.com/pink_9999/pink_99.doc

As you can see the cells ALREADY change colors depending on pass/fail they do not Say PASS or FAIL , depending on the values the cell just changes colors.

Now in order to show wether all the tests have passes, I would show that in one cell at the TOP of the page. How DO I say Pass if all cells are Green and Fail if even One of them is RED

Another example where the cells change to red and green

Thanks again for your help
http://www.geocities.com/pink_9999/pink_999.doc[/img]

Assuming A1 and B2 are fixed and your range is per your attachment:

Try:

=IF(SUMPRODUCT(--($H$6:$H$21<$A$1),--($H$6:$H$21>$B$2))=ROWS($H$6:$H$21),"PASS","FAIL")

 

[pink_99]

color change

Hi

That does not seem to work,,,,can you plz let me know how to check to see if all cells are green (ie pass) based on conditional formating.

Is there a way for me to just check cell color?

 

[agihcam]

how about;

=IF(OR(COUNTIF(H6:H21,"<1000"),COUNTIF(H6:H21,">6500"))=TRUE,"fail","pass")

or

=IF(OR(COUNTIF(H6:H21,"<"&A1),COUNTIF(H6:H21,">"&B2))=TRUE,"fail","pass")

 

[NBVC]

Re: color change

Hi

That does not seem to work,,,,can you plz let me know how to check to see if all cells are green (ie pass) based on conditional formating.

Is there a way for me to just check cell color?

Are you including values that may be equal to A1 or B2?

If so

=IF(SUMPRODUCT(--($H$6:$H$21<=$A$1),--($H$6:$H$21>=$B$2))=ROWS($H$6:$H$21),"PASS","FAIL")

This formula will count how many rows contain values that are both greater than or equal to A1 and greater than or equal to B2. If that matches the total number of rows, then a "PASS" is returned (i.e. all values are between those 2 values)...otherwise a "FAIL" is returned.

to use this in conditional formatting, enter this formula in the Conditional Formatting dialogue box, after selecting Formula Is from 1st dropdown:

=SUMPRODUCT(--($H$6:$H$21<=$A$1),--($H$6:$H$21>=$B$2))=ROWS($H$6:$H$21)