Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Conditionally Format an entire row in Excel 2007 2

Status
Not open for further replies.

Renasant

Technical User
Feb 19, 2009
31
GB
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.
 


Hi,

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

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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?
 
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.
 


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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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.
 



"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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top