Conditional Formatting color changing

We have a spreadsheet that is sent to a manager weekly. The manager takes the sheets from 30+ individuals and copies all into a single workbook that is then distributed to a very large audience and reviewed weekly. In this workbook, I have created drop downs with conditional formatting - Low = Green, Medium = Yellow, Critical = Red. On the original workbook, this formatting works great, however, after the manager consolidates and redistributes all worksheets the Green shows Gray, Yellow is OK and Red shows Black.

Any ideas on what is happening would be appreciated.

Hi cuznleroy

I have taken it as a given that the manager and all the individuals are running on the same version of Excel - no one is taking it home and using an older (or newer!) version.

I have also assumed that you have seen the consolidated version of the spreadsheet rather than just the output therefrom.

I wonder what ranges for CF are shown in the consolidated worksheet (although to be honest, I do not see how this will make a colour difference!)

There - I'm out of ideas.

Regards
Alastair

Good question about the version of Excel. We do have some people using Office 2007 and some using 2010 and will check this.

All managers use the same template, which I just built and posted for all to use. I'm assuming the executive is simply copying and pasting each worksheet into a new workbook which consolidates all into a single workbook, which he then redistributes to all for our weekly review call. I am planning to review the process he is using with him.

When I looked at the CF rule in his workbook, the colors had changed to Black and Gray, but strangely, the Yellow stayed Yellow.

I appreciate you taking time to review and offer your suggestions.