conditional formatting across a whole sheet

**andyr85** · 10-03-2011, 02:22 PM

Hi. I would like to know if I can format a whole sheet easily.

If the value in the cells column 1-6 is below the value in Target it needs to go red, = to needs to be amber and above needs to be green. I can do it a column or row at a time but not sure how to do it for a whole sheet where each row compares it to a different target in column A.

Can anyone help me please!!

**ChemistB** · 10-03-2011, 02:46 PM

Let's go with your example. Select D2:S6

With Excel, you write the equation as it would be for the first cell in your range and Excel will automatically update the references as it goes through your range (if you let it).
So for Red
=A2<D2 at it's simplest. However, you don't want blank cells to show up either so
=AND(A2<D2, ISNUMBER(D2))
But when Excel examines E2 (for example) the conditional formatting would change to
=AND(B2<E2,ISNUMBER(E2)) which you don't want. You need to anchor the column (but not the row for Column A)
Therefore, the final formula would be
=AND($A2<D2,ISNUMBER(D2))
Any questions? Set up the other two similarly. (You won't need the AND or ISNUMBER for the other two)

**Winon** · 10-03-2011, 03:20 PM

Hi ChemistB,

Your formula =AND($A2<D2,ISNUMBER(D2)), causes a circular reference, and would it not be better to change it to =AND($A2<=D2,ISNUMBER(D2)) ? That is if I can figure out how to aplly it without creating a circular reference.

**Mav3r1ck95** · 10-03-2011, 03:56 PM

Hi andyr86,

As an extra thought, you can make the conditional formatting go as far as you want across the sheet to the right, if you so desire - you mentioned formatting a 'whole sheet' a couple of times.

Select rows 2 to 6 and go to Conditional Formatting. Create a new formula rule as

Please Login or Register  to view this content.

and tick 'Stop if True'.

Then create three more formula rules below this rule in the hierarchy.

Please Login or Register  to view this content.

This will carry on formatting as far to the right as there is a number present.
I've just tested this on mine and it works fine.

**Winon** · 10-03-2011, 03:57 PM

Hi AndyR85,

Have a look at Sheet 3 of Book1 in the attached folder

**ChemistB** · 10-03-2011, 04:15 PM

Putting those types of formulas into Conditional Formatting does not cause circular references although I did make an error and it should be
=AND($A2>D2,ISNUMBER(D2))
Take a look at attachment