Forum Discussion
Excel: copying coloured cells only maintaining their data and order
How can I copy coloured cells (with their content), keeping same order to another tab? Eg A cell coloured red with data in rows 1-3 then no colour data for rows 4-6 and then red for 7-10. I want to c...
Can you advise how? I am not familiar with VBA
Sub CopyColoredCells()
Dim sourceRange As Range, cell As Range
Dim targetRange As Range
Dim ws As Worksheet
' Set the source range
Set ws = ThisWorkbook.Sheets("Sheet2") ' Change "Sheet2" to your actual sheet name
Set sourceRange = ws.Range("A2:A20")
' Set the target range
Set targetRange = ws.Range("B2:B20")
' Initialize a variable to track the offset row
Dim offsetRow As Integer
offsetRow = 0
' Loop through each cell in the source range
For Each cell In sourceRange
' Check if the cell has any fill color
If cell.Interior.Color <> RGB(255, 255, 255) Then ' RGB(255, 255, 255) represents the default color (white)
' Copy the cell and paste it in the same row in the target range
cell.Copy targetRange.Cells(cell.Row - 1, 1)
' Increment the offset row
offsetRow = offsetRow
End If
Next cell
End Sub
DM if you need further assistance
Dim sourceRange As Range, cell As Range
Dim targetRange As Range
Dim ws As Worksheet
' Set the source range
Set ws = ThisWorkbook.Sheets("Sheet2") ' Change "Sheet2" to your actual sheet name
Set sourceRange = ws.Range("A2:A20")
' Set the target range
Set targetRange = ws.Range("B2:B20")
' Initialize a variable to track the offset row
Dim offsetRow As Integer
offsetRow = 0
' Loop through each cell in the source range
For Each cell In sourceRange
' Check if the cell has any fill color
If cell.Interior.Color <> RGB(255, 255, 255) Then ' RGB(255, 255, 255) represents the default color (white)
' Copy the cell and paste it in the same row in the target range
cell.Copy targetRange.Cells(cell.Row - 1, 1)
' Increment the offset row
offsetRow = offsetRow
End If
Next cell
End Sub
DM if you need further assistance
- Wow - I'll give it a go. Thank you for the helP