Cell Color Change, Pass/Fail

pink_99

New Member
Joined
Sep 28, 2006
Messages
7
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!!!!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
You need to use Conditional Formatting

Select the cell you want coloured.

Go to Format|Conditional Formatting

Select Formula Is from the 1st drop down menu and enter your conditional formula....e.g. AND(I147="pass",D174="pass")

Click Format and choose your colours.

Click Ok.

If you want to add more conditions, click ADD and continue.
 
Upvote 0
What is the range of these/pass fail values and is the value in the D row always 27 rows lower than the value in the I row?
 
Upvote 0
NBVC:

I think the op is trying to determine if every combination between the 2 cells in all the rows is pass/pass or not.
 
Upvote 0
NBVC:

I think the op is trying to determine if every combination between the 2 cells in all the rows is pass/pass or not.

Oh. You're probably right.

I guess we'll wait 'til the OP replies to your question to be sure.
 
Upvote 0
Cell Color pass/fail

HI

The cells already changes to green or red depending on if it is pass or fail.

I want to know how to show a final cell to say that the test has passed all checks as in the cell there is nothing that says pass or fail, just color change
 
Upvote 0
cells color change pass/fail

Hi

D is not x rows less, I was just giving an example ...the actual eq when it was pass,fail looks someting like this, and there are more than 100 cells to check.

=IF(AND(,J122="pass",K122="pass",L122="pass",M122="pass",P122="pass",Q122="pass",R122="pass",I122="pass",J147="pass",K147="pass",L147="pass",M147="pass",P147="pass",Q147="pass",R147="pass",I147="pass",D174="pass"),"pass","Fail")

Regards
 
Upvote 0
Re: cells color change pass/fail

Hi

D is not x rows less, I was just giving an example ...the actual eq when it was pass,fail looks someting like this, and there are more than 100 cells to check.

=IF(AND(,J122="pass",K122="pass",L122="pass",M122="pass",P122="pass",Q122="pass",R122="pass",I122="pass",J147="pass",K147="pass",L147="pass",M147="pass",P147="pass",Q147="pass",R147="pass",I147="pass",D174="pass"),"pass","Fail")

Regards

You could probably shorten this dramatically by incorporating a Countif()

=IF(Countif(D122:R174,"fail")>0,"fail","pass") where D122:R174 covers your entire range....
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top