Paste Special with Conditional Formatting

[a5972s]

Hi everyone,

I have 200 rows that I do a paste special on. Each day I paste special into another column. The excel sheet has condtional formatting, so that every time a cell has a value of 1, it changes the cell colour. The problem is that although the paste special copies a numerical value, the condtional formatting doesn't work until you F2 & enter.

This means I have to F2 & enter on 200 cells, and I don't know the best way forward.

Any help is greatly appreciated as ever.

 

[wigi]

Can't you do a Paste Special > Values?

 

[a5972s]

No, it doesn't work.

Oddly the cells contain numerical values, but the only way the conditional formatting works is if the cell is edited with an F2 & Enter.

Its creeping me out, because it shouldn't be this complicated!

 

[wigi]

Are these cells formatted as text?
Are the calculations on automatic (as opposed to manual)?

 

[a5972s]

No they aren't formatted as text and yes they are automatic. I've also changed to manual and it makes no difference.

Its the oddest thing I've ever seen!

 

[wigi]

Without seeing the file, I am out of ideas and suggestions. Try uploading a small sample with a filesharing website.

 

[a5972s]

UPDATE: Now sorted it. Even though the cell represented a number in ALL cases, excel saw it as text. I've altered error checking and now its happy.

This is from doing a paste special from one workbook to another.

 

[wigi]

This is from doing a paste special from one workbook to another.

Is it in the same Excel application, or a different one?