Coloring a row

[John K]

I have a spreadsheet and I want to have cells colored from column A to K if
cell h is not blank. So if h3 has a date in it I want A3:K3 to be say light
blue. This is for Office 2003. I can do it with conditional formating in
2007, but my work place doesn't have 2007. I did use column L and put an if
statement to give a true or false in the cell depending on if the cell in
col. h was empty or not. Any ideas how to get this to work?

 

[marcus]

Hi John

This sort of thing will work in 2003 conditional formating. In Cell
A3 go to Format - conditional formattting. Formula is

Paste this

=IF($H3<>"",1,)

Choose your interior colour design.

Now copy this across to K3 and down to where you require it. You will
see that when there is data in Colmn H the corresponding row now turns
into the colour you specified.

This worked fine for me and I am using 2003.

Take care

Marcus

 

[L. Howard Kittle]

Hi John,

This may do what you want, in the worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("H3") Then Exit Sub
Range("A3:K3").Interior.ColorIndex = xlNone
If Range("H3").Value <> 0 Then
Range("A3:K3").Interior.ColorIndex = 37
End If
End Sub

HTH
regards,
Howard

 

[David McRitchie]

"John K" om...

I have a spreadsheet and I want to have cells colored from column A to K if
cell h is not blank. So if h3 has a date in it I want A3:K3 to be say light
blue. This is for Office 2003. I can do it with conditional formating in
2007, but my work place doesn't have 2007. I did use column L and put an if
statement to give a true or false in the cell depending on if the cell in
col. h was empty or not. Any ideas how to get this to work?

You can do it with Conditional Formatting prior to Excel 2007 as well,
as long as you have no more than 3 conditional formatting formulas.
See the following -- I'm sure there is nothing on the page that requires 2007.
Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

 

[FSt1]

hi
you can do it with conditional formating in 2003.
High light the range A3:K3
on the 2003 menu bar>format>conditional formating.....
in the CF dialog enter....
Formula is.......=$H3<>""....... click the format button and pick your color
from patterns.
you can copy the range A3:K3 down as far as you want/need.

Regards
FSt1

 

[John K]

Thanks, that did the trick. Even my IT manager who uses excel all the time
didn't know you could us a conditional format formular over multiple cells.
That saved a lot of time putting in formulars in other columns.

Thanks again