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 copy only the coloured rows with the same spacing
5 Replies
It is also possible to use one of the old Macro 4 commands dressed up within an Excel 365 Lambda function. The worksheet formula could be one of
= IF(MAP(data, GetColorλ)=3, INDIRECT("data"), "") = FILTER(INDIRECT("data"), MAP(data, GetColorλ)=3)In each case the INDIRECT("data") is used in place of data to make the formula volatile and hence recalculate when the sheet calculates.
The Lambda function allows the GET.CELL macro command to work within a defined name rather than on the sheet grid.
"GetColorλ" = LAMBDA(ref, GET.CELL(63, ref) )Note: The workbook has to be saved macro-enabled, as it would for VBA.
- You need to usa VBA for this.
- 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
