Conditionally Format an entire row in Excel 2007 2

[Renasant]

I am trying to conditionally format an entire row in Excel 2007. This used to be easy in 2003, but I cannot make it work in 2007. I am trying to change the color of an entire row based on the value of a cell in that row. My formatting formula looks like this:

Formula: =C6:C44="city name” applies to =$A$6:$D$44

This formula only changes the color of cells A6:A44 when C# is true. Columns B, C and D do not change.

Can one of you experts answer this and maybe even make it into an FAQ? There is not much on Tek Tips about this that I could find.

Please don't tell me about VBA. This is an Excel question in the MS Office forum.

 

[SkipVought]

Hi,

Guessing it might be...
[tt]
Formula: =$C6="city name"
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Renasant]

Changing the formula to:
=$C6:$C44="city name"

(with the relative refernce on the column only) worked. The conditionally format now affects the entire row.

But the question for the FAQ is WHY?

 

[Renasant]

Thanks Skip. I tried that even before your post after seeing the issue earlier today. It works, but why?


Forum Managers: The other conditional formating question from today did not show up on the search for "find a forum". Sorry for an almost duplicate post.

 

[SkipVought]

because =C6:C44="city name" applys to the upper left-hand cell in the SELECTION.

The next cell to the right will then have...
=[red]D[/red]6:[red]D[/red]44="city name" and so forth.

Works just like copying C6:C44 to the cell on the right.

Check out HELP on About cell and range references

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Renasant]

Thanks Skip. Excel calculates left to right across the sheet, so the mixed reference is necessary.

I am reading up on Relative, mixed and absolute cell references.

 

[SkipVought]

"left to right" is not the issue.

The issue is what value is the cell testing against?

Is it a CONSTANT, in which case BOTH row and column are ABSOLUTE.

Are the values in a COLUMN, in which case the COLUMN reference is absolute.

Are the values in a ROW, in which case the ROW reference is absolute.

Is the test against some literal value, in which case none of the reference is absolute.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]