How to use multiple coloured cells in rows as references?

[Ed Harris]

Hello
For this data can anyone tell me how to collect the data in columns P to T for any row where there is a yellow cell or blue cell into new columns so that I can plot them? the problem that I see is that there are duplicates and both types in the same row and I don't know how to add output data onto the bottom of a column. Also for each coloured cell it would be very useful to get the cell address. I have done a few searches trying to work out how to do this but they mostly come up with the same thing - not quite what I need.
Any help much appreciated.

test data 2021.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1																Date	Temp	w speed
2	74.57874	74.57871	74.57871	74.5788	74.57877	74.57878	74.57885	74.57885	74.57889	74.57889	74.57886	74.57885	74.57889	74.5789	74.57892	16/03/2021 07:31	14	2.9	0	1
3	74.57773	74.57764	74.57779	74.57777	74.57775	74.57775	74.57781	74.57775	74.57778	74.57777	74.57784	74.57781	74.57793	74.57797	74.57796	16/03/2021 07:35	14.2	5.8	1	0
4	74.57793	74.57796	74.57807	74.57805	74.57787	74.5779	74.57792	74.57791	74.578	74.57813	74.57797	74.57801	74.578	74.57804	74.57809	16/03/2021 07:39	14.2	3.2	0	1
5	74.57954	74.57955	74.57937	74.57952	74.57945	74.57958	74.57951	74.5794	74.57952	74.57952	74.57943	74.57952	74.57935	74.57947	74.57938	16/03/2021 07:44	14.2	1.9	1	0
6	74.58018	74.58018	74.58024	74.58026	74.58019	74.58019	74.58022	74.58024	74.58019	74.58022	74.58021	74.58028	74.58026	74.58036	74.58023	16/03/2021 07:48	14.3	2.3	0	1
7	74.57895	74.57896	74.57901	74.57901	74.57905	74.57919	74.57907	74.57907	74.57907	74.57912	74.57906	74.57904	74.57909	74.57909	74.57903	16/03/2021 07:53	14.3	3.9	1	0
Sheet1

 

[mikerickson]

How do the cells get their color? User action or conditional formattting?

 

[Ed Harris]

How do the cells get their color? User action or conditional formattting?

User action from the standard colours.

 

[mikerickson]

Excel is not built for considering color a data. It's a visual highlight for human use only.
You might try this code

Sub Test()
    Dim oneCell As Range
    Dim SourceRange As Range
    Dim DestinationRange As Range
    
    Set SourceRange = Sheet1.Range("A1"): Rem adjust
    Set DestinationRange = Sheet2.Range("a1"): Rem adjust
    
    For Each oneCell In SourceRange.CurrentRegion
        If oneCell.DisplayFormat.Interior.Color <> vbWhite Then
            With DestinationRange.EntireColumn.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                oneCell.Copy Destination:=.Cells(1, 1)
                .Cells(1, 1).Value = oneCell.Value
                .Cells(1, 2).Value = oneCell.Address(, , , True)
            
            End With
        End If
    Next oneCell
End Sub

 

[Ed Harris]

Excel is not built for considering color a data. It's a visual highlight for human use only.
You might try this code

Sub Test()
    Dim oneCell As Range
    Dim SourceRange As Range
    Dim DestinationRange As Range
  
    Set SourceRange = Sheet1.Range("A1"): Rem adjust
    Set DestinationRange = Sheet2.Range("a1"): Rem adjust
  
    For Each oneCell In SourceRange.CurrentRegion
        If oneCell.DisplayFormat.Interior.Color <> vbWhite Then
            With DestinationRange.EntireColumn.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                oneCell.Copy Destination:=.Cells(1, 1)
                .Cells(1, 1).Value = oneCell.Value
                .Cells(1, 2).Value = oneCell.Address(, , , True)
          
            End With
        End If
    Next oneCell
End Sub

Thats great, thanks for the effort. Unfortunately it stopped at destination range with the error "object required" so I tried changing it to:
Set DestinationRange = Sheet1.Range("BK1"): Rem adjust
with the result that nothing changed when I ran the macro.
By the way Is there a better way to label cells, for referencing, other than colour as I am having to do that manually due to the amount of judgment required to categorise them?

 

[mikerickson]

What you might do is have a double click to both color them and move them to the other sheet. But that would pretty much restrict you to one color.