Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop spilling the text to the adjoining cell | Excel Discussion (Misc queries) | |||
insert rows in a worksheet that do not change adjoining rows | Excel Discussion (Misc queries) | |||
if cell is blank insert a zero | Excel Discussion (Misc queries) | |||
automatically date a cell when entering data in adjoining cell | Excel Programming | |||
How do I display contents of an adjoining cell? | Excel Worksheet Functions |