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...
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.