Conditional Format 1 cell based on other cells

**dbrine** · 02-08-2009, 05:51 PM

I have a spreadsheet (Attached), I track parts and I have conditional formatting setup, 1 or 0 parts on hand and when the part is a 1 I have a green icon, 0 I have a red icon. no problem. But I want to show an overall status in a different cell. Green/ Red or whatever. How do I do this?

**JBeaucaire** · 02-08-2009, 07:43 PM

If you wanted F5 to watch the status of E5, open the conditional formatting for F5 and use:

Condition1: Formula Is: =$E5=1
...and set your color

Condition1: Formula Is: =$E5=0
...and set your color

**dbrine** · 02-09-2009, 12:56 AM

Thank you. I want D3 to watch the status of several cell in this one (E5:E12). I use Excel 07, can you tell me where the formula is, is at? I don't see it

**JBeaucaire** · 02-09-2009, 01:14 AM

Conditional Formatting in Excel 2007

VIDEO - Conditional Formatting in Excel 2007

As for the formula to watch that many cells, you need to more clearly explain. Watching 8 cells is not the same as watching 1 cell. How do you want the 8 cells evaluated? Are all 8 cells to receive a 1 or a 0, and are you looking for a conditional format color change if ANY cell has a 1 or only if all of them do? Or 2...or 3?

**dbrine** · 02-09-2009, 02:43 AM

The 8 cells that are being watch, if any of those cells is a 0 then the status cell turns red otherwise it's green.

**JBeaucaire** · 02-09-2009, 03:01 AM

Then you want to sum the range of cells and if it's anything less than 8, turn red:

Condition1: Formula Is: =SUM(E5:12)=8
...set the color to Green.

You don't need a "red" condition because you should set those cells to have a red background all the time and only override the "green" when all the cells are entered with a 1, signifying the row is complete.

**dbrine** · 02-09-2009, 08:35 AM

Great! I actually did that. How do I hide the number in the status (sum box), change font color? One last question about this. I need to compare the numbers in one column to the other column, the qty in the one column varies from 0 to whatever. How do I do a comparison to see if it's equal and if not make it red? I can do it for a single cell but not for a range of cells. I'm using excel 07

**JBeaucaire** · 02-09-2009, 02:04 PM

When doing conditional formatting, before you even try, go to a blank cell and see if you can create a simple TRUE/FALSE formula. If you can, then use the "test" portion of the formula you create as your formula in Conditional Formatting.

So, to test if A1 and B1 are equal, just go to C1 and try to get a "true/false" response.

=IF(A1=B1,true)

Remember, you want to get a TRUE response for the status that causes the color change. In this instance it's not right because you want it TRUE when A1 and B1 are NOT the same, right? So you try again:

=IF(A1<>B1,TRUE)

Once you get it working in a cell, take the "test" part and put into conditional formatting as a formula:

Condition1: Formula Is: =A1<>B1
...and set the color response for the TRUE state....in this case red.

Always get the formula working first in a regular cell, and you'll never get confused when you go to implement the CF.