VBA to return values based on cell formats

Hi, thanks for looking in.

I have a data table for which I need to return start and end date values based on the format of the cells in the table.
The table is shown in the image attached. Essentially it is a simple workflow table with peoples names on the y-axis and dates of when work commences and ends on the x-axis. The data array cells are then color coded to reflect start and end times for each resource. I need the macro to read across the table find the first blue(color index 33) cell then lookup up the date in the header row, this is the start date, then populate this in the start date cell. Then move across the row until the last interior shaded blue cell and lookup the date on the header row which is then the end date for that resource which should populate the "end date" cell. The code should then loop through all rows before ending.

XL help.png

Somebody has already very kindly provided me with some code to solve this problem. The code is:

Please Login or Register  to view this content.

I copied and pasted the code into the workbook, but when I ran the macro nothing appears to happen. I tried stepping into the code but it doesn't reveal any errors. I'm sure this is something obvious which I'm not doing right. I have an intermediate knowledge of VBA and I have tried to understand the code provided and I can follow most of it, but I can't see where it defines variables "A", "B" and "C" nor can I see where it enters the start and end date values to the results cells ?

Please can you help fix the code. As I say it is no doubt down to my lack of knowledge rather than a problem with the code itself.
Many thanks
H

Thanks AlphaFrog and watersev for all your help.

Using the "Application.FindFormat.Interior.Color = 15773696" line the code ran perfectly.
I wonder why it didn't work for me using 33 as it did for AlphaFrog?

If "15773696" is some kind of boolean translation of color 33 then how do I get a list of colors in the "long format", is there a piece of code that will tell me the "long format" of the interior color?
Final question, how would the code differ if it were based on conditional formating of the cell rather than user defined color formatting?


Thanks again both, I really appreciate your help.
Kind regards H.