enigmatik
07-08-2016, 10:55 AM
Excel Version: 2016
Error: Run Time Error 424
Hello All,
Please forgive me for being very new to VBA. I am trying to select cells in a particular column by color, and copy both the color coded cell and the cell directly above it into a different worksheet. I would like for the color coded cell to be in one column and the cell from the row above to be in the next column. I know that the line of code telling excel to copy is completely wrong. Any advice i could get would be greatly appreciated!
Sub CopyColor() Dim rCell As Range
ActiveSheet.Name = "RawData"
Sheets.Add.Name = "Results"
Sheets("RawData").Activate
For Each rCell In ActiveSheet.UsedRange
If rCell.Interior.ColorIndex = -4142 Then
rCell.Offset(-1, 0).Resize(2, 1).Copy Destination:=Results.Range(A).End(xlUp)(2, 1)
End If
Next rCell
End Sub
Error: Run Time Error 424
Hello All,
Please forgive me for being very new to VBA. I am trying to select cells in a particular column by color, and copy both the color coded cell and the cell directly above it into a different worksheet. I would like for the color coded cell to be in one column and the cell from the row above to be in the next column. I know that the line of code telling excel to copy is completely wrong. Any advice i could get would be greatly appreciated!
Sub CopyColor() Dim rCell As Range
ActiveSheet.Name = "RawData"
Sheets.Add.Name = "Results"
Sheets("RawData").Activate
For Each rCell In ActiveSheet.UsedRange
If rCell.Interior.ColorIndex = -4142 Then
rCell.Offset(-1, 0).Resize(2, 1).Copy Destination:=Results.Range(A).End(xlUp)(2, 1)
End If
Next rCell
End Sub