Shade cell if No value in offset cell

[Corey]

I am trying to have a cell in Column "E" shaded IF a cell in Column"B" has no value.
I do not want it to pickup all the empty cells in Column "B" as my data stops at Row B59.

I want something like this:

Sub ShadeCell()
With Sheet1
If range("B:B").value = "" and range("B:B").row < 59 then
..activate
activecell.offset(,-1,2).select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
end with

end sub


Can someone assist me to have this work?

Corey...

 

[carlo]

Why don't you take conditional format?

choose your cells in Column E (E1:E59) with E1 active, then go to
Format -> Conditional Formatting...
There you choose "formula" and enter: =B1=""
And then select a grey background.

hth
Carlo

 

[Ken Johnson]

If Conditional Formatting is to be avoided then maybe...

Sub ShadeCell()
Dim rgC As Range
For Each rgC In Sheet1.Range("B2:B" & 59)
If rgC.Value = "" Then
With rgC.Offset(-1, 3).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Else
With rgC.Offset(-1, 3).Interior
.ColorIndex = xlNone
End With
End If
Next
End Sub

However, are you sure about your Offset argument, Offset(-1,2) only
goes to column D, so I changed to Offset(-1,3) to affect column E. And
that -1 seems dubious, but I left it alone, so it still affects the
previous row.

Ken Johnson

 

[Corey]

Carlo,
how do "choose your cells in Column E (E1:E59) with E1 active"?



Why don't you take conditional format?

choose your cells in Column E (E1:E59) with E1 active, then go to
Format -> Conditional Formatting...
There you choose "formula" and enter: =B1=""
And then select a grey background.

hth
Carlo

 

[carlo]

Sorry for my bad explanation.

Select Range E1:E59
one of the cells should be in a slightly different color, this is the
active cell.
Activecell is always the first cell you selected in the range.
You could also click on E1 and then shift+click on E59 which would
result in the same.

hth
Carlo