MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Change text color of a row if data entered is...


Posted by Steve on January 12, 2001 8:00 AM

I currently have this piece of code which only changes the text color in one cell of a row: (totals is in column T or 20 of the current row)
====begin
If (Left(Cells(Row, 2), 2) = "01") And (Cells(Row, 7) <> "") Then
totals = totals + 33
Cells(Row, Col).Font.ColorIndex = 5
End If
====end
I would prefer the whole row to have the text color changed. Any idea how to do this?

Thanks, Steve


Posted by Tim Francis-Wright on January 12, 2001 8:52 AM

Change the line before End If to:-
Intersect(ActiveSheet.Usedrange,Cells(row,1).EntireRow).Font.ColorIndex = 5

This will set the font color for the entire row
(to the extent of the UsedRange for the sheet)
to blue.

You might instead want to use conditional
formatting (Excel 97 or later), which would
keep you from having to undo the cell formatting
else where in the macro.

If you were to start in Row 2, then the formatting
for one cell would be (Format/Conditional Formatting) :-
=AND(LEFT($B2,2)="01",$G2<>"")
You would then hit the Format button, and
select the Font Color on the Font tab.

You could then copy this format to the rest
of the relevant cells on the sheet. It's a bit
of a pain if you have a lot of formats in the
range, because you'll need to redo them, but
it might be worth it anyway.

Good luck!

Posted by Steve on January 12, 2001 11:36 PM

Thanks Tim, this worked just fine!

Steve