[XL07] Conditionally format fill color for entire row

[Trevor Stone]

Hi All,

Is it possible to conditionally format the fill color for an entire
row based on a cell reference?

For instance,

Columns 1,2,and 3 are text but column 4 is a number. I'd like to fill
the whole row blue if the number in that row equals 100.

Then I think I can use the painter to copy the formatting to the rest
of the sheet.

Any ideas would be great.

Thanks,

Trevor

 

[Bernard Liengme]

Firstly: I would not apply this to the WHOLE row as XL2007 has 16,384
columns
Second: why do you speak of 'column 4' rather than 'column D'?
Third: here is how to do what you asked

Select A1:K10
Use Home | Conditional Formatting | New Rules | "Use a formula ....."
In dialog the formula to use is =$D1>=100 (or =$D1=100, as preferred) and
format as required
Note the 1 refers to row 1, so if you are using another starting row, adjust
as needed. The $ is essential for your purpose.

Note you can now use Conditional Formatting | Manages Rules and in the new
dialog you can specify the range to which the rule applies

best wishes

 

[M Kan]

Use a conditional format, Formula Is and tie it to your target cell. Once
you've verified it works, click the format painter then the row.

 

[TWhizTom]

Trevor:

You would NOT want to fill the entire row, unless you really do have ALL
those columns containing data....

Instead, name the range you want to fill and setup vb code on the Page
change event to check for 100 in the field, if yes, fill it...
if range("A1:A1") = 100 then
With MyNamedRange
.Interior.Color = RGB(0, 0, 255)
.Font.Color = RGB(0, 0, 255)
End With
End If

Assuming you have multiple lines like this setup a for next loop to inspect
each range and cell.

 

[Derf]

I have a related problem. In trying to change the color of the entire row
based on the contents of one cell in that row, only the first column gets
changed. The format indicates that it applies to $A$3:$D$22. What have I done
wrong?

Thanks

 

[Peo Sjoblom]

How does the formula look under formula is ?

--


Regards,


Peo Sjoblom

 

[Derf]

The formula reads as =D3="VG" and it applies to =$A$3:$D$27

Only the "A" column gets formatted.

Did I provide what you needed?

Thanks.

 

[David Biddulph]

You need to understand the difference between relative and absolute
addressing.
Having seen what you've got in your formula for formatting A3, have a look
at what you've got as the formula for cell B4, for example.

If you are trying to apply the format to all columns as a result of the
content of column D, then in your original formula change your =D3="VG" to
=$D3="VG"

 

[Spiky]

Another way is to select all of the cells to be formatted first, then
pick Conditional Format from the menu.

 

[Spiky]

Another way is to select all of the cells to be formatted first, then
pick Conditional Format from the menu.

Sorry, didn't finish my own post.

I mean, select them all first, because then Excel should do the
absolute/relative reference properly automatically.

 

[Derf]

Oops, I should have seen that myself. My age must be having an effect. Thanks
for your help!

 

[Derf]

I tried this method, and it formatted the entire selection without regard for
the conditional part. Obviously, I may have erred in the way I did it.

I do have the problem handled via the post from David.

Thanks for your input.