Someone please tell me there's an easier way to accomplish this task. I have a master data worksheet, which is 'rolling' data (ie one worksheet updated daily; backed up but not saved at a static point in time). Each week I run a "Project G" macro, which creates a subset of data - columns and rows. I need to issue the Project G worksheet with colour coding on various dates (missed forecasts, delays, wins etc) compared to last week.
At the moment I'm thinking:
1. Create "Project G" worksheet.
2. Prompt the user to open last week's report. I have no control over where these reports are saved or what they are called.
3. Copy "G-Hide" from last week's report to this week's report (see further down for creation of "G-Hide). Rename "G-Hide" to "G-LW" (ie last week).
4. Run the conditional formatting on "Project G", comparing against "G-LW".
5. Copy and hide "Project G" in the same workbook, called "G-Hide", to ensure I have an untampered, static record of the data as it was at the point of creation. (I have no control over who might use the report and accidentally (or otherwise) change data.)
In this scenario, I end up with three worksheets - "G Project" (visible), "G Hide" (this week's data, hidden), "G-LW" (last week's data, hidden). If it's possible to 'hard wire' conditional formatting using VBA, then I could delete "G-LW" once everything was formatted.
It sounds so easy when I list it out like that, but I know it's not going to be quite that straight forward. For starters, I have no 'record id' field. I will have to create a concatenation field to ensure I'm comparing the right records. Although, if I create that on the "Project G" worksheet, it will copy across each week.
Any comments, thoughts, suggestions. (Can't say I'm brimming over with enthusiasm and positivity about programming this.)
At the moment I'm thinking:
1. Create "Project G" worksheet.
2. Prompt the user to open last week's report. I have no control over where these reports are saved or what they are called.
3. Copy "G-Hide" from last week's report to this week's report (see further down for creation of "G-Hide). Rename "G-Hide" to "G-LW" (ie last week).
4. Run the conditional formatting on "Project G", comparing against "G-LW".
5. Copy and hide "Project G" in the same workbook, called "G-Hide", to ensure I have an untampered, static record of the data as it was at the point of creation. (I have no control over who might use the report and accidentally (or otherwise) change data.)
In this scenario, I end up with three worksheets - "G Project" (visible), "G Hide" (this week's data, hidden), "G-LW" (last week's data, hidden). If it's possible to 'hard wire' conditional formatting using VBA, then I could delete "G-LW" once everything was formatted.
It sounds so easy when I list it out like that, but I know it's not going to be quite that straight forward. For starters, I have no 'record id' field. I will have to create a concatenation field to ensure I'm comparing the right records. Although, if I create that on the "Project G" worksheet, it will copy across each week.
Any comments, thoughts, suggestions. (Can't say I'm brimming over with enthusiasm and positivity about programming this.)
