VBA to pull out all colored cells from a row to a different sheet

[nc_waggoner]

Good morning. I will try and explain this as thoroughly as possible. I have a sheet with Dates as rows and test locations as columns (just fyi...there are about 275 columns of testing points). It is conditionally formatted so anything out of control gets highlighted in red as seen on 6/5. Please note that these will also be values, I just wanted to give a couple examples to extract. What I am looking for is a way to type in a date in the selection cell (call it B1...Date starts at A3 for reference) and pull out all columns that are highlighted in red for that day on a separate sheet...in the below example this would be Locations 1 & 3. It is basically like a daily report to show which locations need attention. For this purpose, let's say this is on "Sheet 1" and I would like it to display on "Sheet 2".

[TABLE="width: 569"]
<tbody>[TR]
[TD]SELECTION[/TD]
[TD]6/5/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]LOCATION 1[/TD]
[TD]LOCATION 2[/TD]
[TD]LOCATION 3[/TD]
[TD]LOCATION 4[/TD]
[/TR]
[TR]
[TD]6/1/2017[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]6/2/2017[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]6/3/2017[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]6/4/2017[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]6/5/2017[/TD]
[TD]RED[/TD]
[TD]10[/TD]
[TD]RED[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]6/6/2017[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]6/7/2017[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]6/8/2017[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
</tbody><colgroup><col><col span="4"></colgroup>[/TABLE]

 

[Rick Rothstein]

How is/should the data be arranged on Sheet2? Are there Location headers there and do the values go in the same Location column they currently are in? Or do you just want the values placed next to each other no matter what columns they came from?

 

[nc_waggoner]

Great question...I have not finalized the report format. But if possible I would imagine just a list of the Locations in a vertical format, for example in Column A on sheet 2:

A1 6/5/2017
A2
A3 Location 1
A4 Location 3