I have some reports and most of them involve tracking information by date.

When I copy and paste from the original spreadsheet(a report I have exported from Access to Excel...) I have to re-type every single date in the column or Conditional Formatting refuses to color it, like it isn't recognizing the data in the columns.

Visually the text appears the same before I type it and it will not color as it des after I type it and it will color.

I am so frustrated with this it is beyond words, and searching google has lead me nowhere time and time again. Lots of issues with copying and issues with colors but nothing even comes close to solving this problem.



I have tried changing the "format" of the column to text, to numbers... I have tried copying the original and pasting it with just "Values" selected...

Using Excel 2007
Windows 7 64bit

Welcome to the forum.

This sounds very frustrating so hopefully we'll be able to help you.

What is your conditional formatting formula?
When you copy and paste the values from the report into your worksheet, do the cells show a green triangle in the top right of the cell?

__________________
Colin

RAD Excel Blog

Thank you!

When I was adding the conditional formatting, I was clicking on the Conditional Formatting button on the ribbon, highlighting "Color Scales" from the drop down, and then choosing any one of the default color scales displayed.


When I copy and paste the values there is no green triangle.

After copying and pasting from the original spreadsheet, using Paste Special:Values, input a 1 into any unused cell. Copy the cell, then select your date range and use Paste Special:Values > Multiply.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I'm sorry, I copied, then used Paste Special Values, I put a 1 into a random empty cell, copied that cell, then selected the date range and clicked Paste Special and selected Values and Multiply. I either got a column of nonsense (41000, 41001, 41001, 41001, 41002, 41003, 41003 ect...) or a column of zeros.

Is there something I did wrong there or something I am missing? And do I paste next to the column I used the first Paste Special Values in or do I paste over that first Paste Special Values? When I pasted next to it, all I received were 0's, when I pasted over the first Paste Special Values I received the 41000, 41001 ect.

Those numbers are the serial values for the dates. I'd have thought you'd only see those numbers if the cells weren't already formatted for dates. Simply change the formatting to the appropriate date format and you you should see the correct results.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

That makes sense! This was easier than anticipated. Thank you very much for your help.