Using Index(Indirect(Match)) formula and need output to retain original formatting

Hi All,

I'm VERY new to VBA and pretty decent with formulas. The attached .xls file shows an example of what I'm trying to accomplish (actual set-up, but bogus data). I have three sheets, "Compare", "Jun-15", and "Jul-15". The Jun and Jul worksheets have data with formatted cells with color. In the Compare worksheet I want to put the data side by side for easy visual comparison. I've got the formulas figured out to pull the data, but I want the returned values to retain their color formatting from the Jun and Jul worksheets. This is something I will do monthly, which is why I used "Indirect" in the formula, so I could easily update the months on my Compare sheet and have it automatically update data based upon whatever months I select.

Hope that makes sense. From what I can tell, I need to write a VBA code in order to do that, but I haven't been able to figure it out or find exactly what I need on the forum.

If someone knows how to do this, it would be GREATLY appreciated!!!
Project Comparison.xlsx

Hi there,

You've probably considered and rejected this approach already, but I'll ask anyway!

Have you thought about using Conditional Formatting to highlight cells on the monthly data worksheets, and then using the same Conditional Formatting conditions on the Compare worksheet?

Regards,

Greg M

Greg M,

Yes, I have considered that, but my raw data does not have a hard and fast rule I could list for what makes the cell red, yellow, or green. The output is the # of activities in a particular schedule to do not meet the metric and the color is based the % of activities not meeting the criteria. However, my raw data just provides the # of activities not meeting the metric and the color. I'm considering going back and adding in a column for the total # of activities on the project so I can then build a conditional format formula tied to the percentage. I am also trying to increase my capabilities in excel and thought someone on the forum would know how to use VBA to do that.

Thanks for your reply though!
Ldy4x4jeep

Hi Ldy4x4jeep,
try this

Please Login or Register  to view this content.

Hi Nilem,

That worked perfectly!!! Thank you so much! I can't wait to get to know VBA well enough to do that on my own.

Thanks again!
Ldy4x4jeep

Hi again,

Thanks for your feedback - glad you were able to get a solution on here.

regards,

Greg M