Conditional formatting for the entire spreadsheet

[cjbrown815]

Hello,
I have an excel 2010 spreadsheet, I need it to highlight all cells in a row red if the column "O" is less then 0 (zero) and green if its equal to or greater than o (zero).
This was working, but as new records are added, the conditional formatting doesn't update the new rows. So I need to use absolutes, but not sure how.

thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

Hi,

You want conditional formatting for the table on your sheet.

Simply make your table a Structured Table. When you define the CF criteria, it will use the TABLE as the target range, regardless of whether you might have a fewer or greater number of rows.

Insert > Tables > Table.

Structured Tables are a huge beneficial feature as of Excel 2007. EVERY Excel user ought to know how to employ this powerful feature!!!

 

[cjbrown815]

I tried this, the table is named, its not working. please be instructional


-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

So you have converted your table to a Structured Table.

NOW set your CF within the table by 1) selecting entire column data range(s) to CF, 2) start the CF wizard, 3) apply the CF criteria/format.

 

[zelgar]

In have the feeling you want the following options
1. Cells without any formatting if blank
2. Cells red if negative
3. Cells green if zero or positive

When you add a conditioning format rule, it is above the old rules. Therefore, to prevent the blank cells from being colored green (for being 0) do the following:
1. From the Home Ribbon, Conditional Formatting, New Formatting Rule, Use a formula to determine which cells to format
2. For the formula for Format values where the formula is true put =O1>=0
3. Click the Format and select Fill and the color green.
4. Click OK twice
5. Repeat Steps 1 - 4 with the following changes: Formula =O1<0, and fill color RED
6. Repeat Steps 1 - 4 with the following changes: Formula =ISBLANK(O1) and fill color to No Color
7. From the Home Ribbon, Conditional Formatting, Manage Rules
8. You should see 3 lines of with different conditional formatting with the blank one on top and the green on the bottom
9. For each of the rules change the Applies to range to =$O:$O and click OK
10. It's only important that the blank formatting is above the green formatting. You can always change the order of which item is above by using the little arrows in the Conditional Formatting Rules Manager