Need loop help
 

I have a spreadsheet that I need to read the D column all the way down. If
the cell in that column is pink(38) then I would like to take the contents
of that cell(not the color) and make it go up 1 and left 1. I was thinking
that I could use R1C1 format with something like R-1C-1 but I am unsure how
to do it. Can someone help me? Here is what I have so far.

For i = 1 To lastRow
Set currCell = ActiveWorkbook.Sheets("Sheet1").Cells(i, 4)
If currCell.Interior.ColorIndex = 38 Then
' need to take the contents and put it in the cell one up and
one left.
Else
MsgBox "Cell D" & i & " is NOT 38"
End If
Next

Thanks,
-Steven-

Need loop help
 

Maybe...

with activeworkbook.worksheets("sheet1")
For i = 2 To lastRow
If .cells(i,4).Interior.ColorIndex = 38 Then
.cells(i-1,3).value = .cells(i,4).value
'or
'.cells(i,4).offset(-1,-1).value = .cells(i,4).value
end if
next i
end with

I started with row 2--1 up from row 1 is a problem.


Steven Platt wrote:

I have a spreadsheet that I need to read the D column all the way down. If
the cell in that column is pink(38) then I would like to take the contents
of that cell(not the color) and make it go up 1 and left 1. I was thinking
that I could use R1C1 format with something like R-1C-1 but I am unsure how
to do it. Can someone help me? Here is what I have so far.

For i = 1 To lastRow
Set currCell = ActiveWorkbook.Sheets("Sheet1").Cells(i, 4)
If currCell.Interior.ColorIndex = 38 Then
' need to take the contents and put it in the cell one up and
one left.
Else
MsgBox "Cell D" & i & " is NOT 38"
End If
Next

Thanks,
-Steven-

--

Dave Peterson

Need loop help
 

Hi Steven,

Try:

'=================
Public Sub Tester()
Dim Lastrow As Long
Dim i As Long

Lastrow = Cells(Rows.Count, "D").End(xlUp).Row

For i = 2 To Lastrow
With Cells(i, "D")
If .Interior.ColorIndex = 38 Then
.Offset(-1, -1).Value = .Value
End If
End With
Next i

End Sub
'<<=================

---
Regards,
Norman



"Steven Platt" wrote in message
...
I have a spreadsheet that I need to read the D column all the way down. If
the cell in that column is pink(38) then I would like to take the contents
of that cell(not the color) and make it go up 1 and left 1. I was thinking
that I could use R1C1 format with something like R-1C-1 but I am unsure how
to do it. Can someone help me? Here is what I have so far.

For i = 1 To lastRow
Set currCell = ActiveWorkbook.Sheets("Sheet1").Cells(i, 4)
If currCell.Interior.ColorIndex = 38 Then
' need to take the contents and put it in the cell one up and
one left.
Else
MsgBox "Cell D" & i & " is NOT 38"
End If
Next

Thanks,
-Steven-

Need loop help
 

Thank you guys so much .. both of you. I was stuck trying to incorporate
some of that crazy R1C1 non-sense. Thanks again.

-Steven-
"Dave Peterson" wrote in message
...
Maybe...

with activeworkbook.worksheets("sheet1")
For i = 2 To lastRow
If .cells(i,4).Interior.ColorIndex = 38 Then
.cells(i-1,3).value = .cells(i,4).value
'or
'.cells(i,4).offset(-1,-1).value = .cells(i,4).value
end if
next i
end with

I started with row 2--1 up from row 1 is a problem.


Steven Platt wrote:

I have a spreadsheet that I need to read the D column all the way down.
If
the cell in that column is pink(38) then I would like to take the
contents
of that cell(not the color) and make it go up 1 and left 1. I was
thinking
that I could use R1C1 format with something like R-1C-1 but I am unsure
how
to do it. Can someone help me? Here is what I have so far.

For i = 1 To lastRow
Set currCell = ActiveWorkbook.Sheets("Sheet1").Cells(i, 4)
If currCell.Interior.ColorIndex = 38 Then
' need to take the contents and put it in the cell one up and
one left.
Else
MsgBox "Cell D" & i & " is NOT 38"
End If
Next

Thanks,
-Steven-

--

Dave Peterson