If blank, insert adjoining cell value
 

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

If blank, insert adjoining cell value
 

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


"CLR" wrote:

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

If blank, insert adjoining cell value
 

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

--
Gary''s Student - gsnu200714


"CLR" wrote:

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

If blank, insert adjoining cell value
 

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





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


"CLR" wrote:

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

If blank, insert adjoining cell value
 

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


"Gary''s Student" wrote in message
...
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

--
Gary''s Student - gsnu200714


"CLR" wrote:

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