Conditional Formatting not applied to data exported from Access

[Guest]

Hi

I have an MS Access report that I copy+paste into an excel worksheet. Other
cells in the worksheet contain conditional formatting which lookup the cells
with the access data (date: dd/mm/yyyy) and compares against a date in two
other cells and shades the cell appropriately.

The problem is not the formula - it works fine - but that the conditional
formtting does not work on the data exported from Access but only works if I
manually type in a date. However, I cannot see ANY difference between an
manually tpyed in date and the Access exported date.

Help please!
Mark

 

[Max]

Perhaps this might work for you ..

Select the column of "dates"
Data > Text to Columns
Click Next > Next
In step 3, under "Column data format":
Check "Date" and select the format "DMY" from the droplist
Click Finish

 

[Guest]

Thanks. That works - though I don't really understand why.
It is a shame that whatever formatting Excel applied when you run "Text to
Columns" doesn't persist in the worksheet. i.e. when I paste in new data from
Excel I have to run Text to Columns again. Not a big problem - I have created
a macro to do this and stuck a button in the menu bar.

Thanks verymuch for the v. quick reply!

 

[Max]

Glad it worked, Mark !
Thanks for the feedback ..

 

[Guest]

I have now hit another wall in terms of my know-how.
I now have a number of excel spreadhseets with the macro and toolbar button
I created as described before.
I want to save the spreadhseets on a shared drive for colleagues to use. How
do I associate the menu button and macro with these spreadhseets? If I copy
the files locally then the spreadhsset can no longer find the macro.
other psots/websites suggest using an .xlt or addin. But I cannot get former
to work nor figure out the latter.

Thanks again
Mark

 

[Max]

Sorry I don't know, Mark.
Hang around for insights from others ..