If blank, insert adjoining cell value

[CLR]

Hi All..........
Having a tough time here, I would like to scan column J of the activesheet
from unknown bottom up to and including Row 2 and if each cell is blank, to
insert the value in the adjoining cell in column I, (a date or also a
blank), and at the same time, color the background of that cell red. Any
help would be appreciated.

Vaya con Dios,
Chuck, CABGx3

 

[Barb Reinhardt]

Sub Test()
Dim myRange As Range
Dim r As Range
Dim lcol As Long
Dim lrow As Long


lcol = 10
lrow = Cells(Rows.Count, lcol).End(xlUp).Row
Set myRange = Cells(2, lcol)
Set myRange = Cells(2, lcol).Resize(lrow - 2 + 1, 1)
For Each r In myRange
If IsEmpty(r) Then
r.FillRight
r.Interior.ColorIndex = 3
End If
Next r

End Sub

 

[Gary''s Student]

How about:

Sub clr()
For i = Cells(Rows.Count, "J").End(xlUp).Row To 2 Step -1
If IsEmpty(Cells(i, "J")) Then
Cells(i, "J").Value = Cells(i, "I").Value
Cells(i, "J").Interior.ColorIndex = 46
End If
Next
End Sub

 

[CLR]

Thats cool Barb, thanks...........it works better than anything I got
now.......just one little quirk tho........if column I has data down further
than column J, it just ignores the bottom part of column J...........is
there an easy fix for that?

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Cool Gary"s Student, thank you, but it also has the same problem as Barb's,
in that it ignores the bottom of column J if column I is longer than column
J

I somehow need the row count to be figured from the longer of I or
J..........possible?

Oooops, never mind, I can use column A........it's always equal to the
longest.......

THANKS A BUNCH TO BOTH YOU AND BARB..........

Vaya con Dios,
Chuck, CABGx3