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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

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.
 

pink_99

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

pink_99

New Member
Joined
Sep 28, 2006
Messages
7

ADVERTISEMENT

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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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....
 

pink_99

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

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Forum statistics

Threads
1,136,650
Messages
5,676,996
Members
419,667
Latest member
MegEri

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
Top