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...

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

On Jan 21, 10:31*am, "Corey" wrote:
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...

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

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



"carlo" wrote in message
...
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

On Jan 21, 10:31 am, "Corey" wrote:
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...

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

On Jan 21, 12:13*pm, "Corey" wrote:
Carlo,
how do "choose your cells in Column E (E1:E59) with E1 active"?

"carlo" wrote in message

...
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

On Jan 21, 10:31 am, "Corey" wrote:



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...- Hide quoted text -

- Show quoted text -