I work in quality for a major US company. We have a contracted 3rd-party company do our surveys, and I can extract all the responses (which are numeric) in a bulk format (tab-delimited). My goal is to manipulate the data for an easy-to-understand summary (Excel 2013 format) which I have created.

I spent a lot of time writing Excel macros to delete unnecessary columns, transpose the data, insert blank rows, etc. Everything that is needed so the bulk output lines up perfectly with my summary, so all I would have to do is copy/paste the final result into my summary which has conditional formatting to insert colored bubbled for certain numeric ranges (just the kind of at-a-glance stuff VPs love).

All fine so far, except that I find when I paste the range in the conditional formatting does nothing. I should note the following:

- I Paste Special as Values only (i.e. I am not overwriting the formatting).
- All cells are formatted as General, all responses are numbers only (0 to 10).
- If I do nothing more than manually overwrite the number in a cell with the same number the conditional formatting instantly appears for that cell only.
- Similarly, if I highlight a cell and press F2 + Enter the conditional formatting instantly appears for that cell only.

I've searched the web high and low and found lots of conversation on this topic, but most seems related to formulas (my Calculation is set to Auto, but it makes no difference). Mine is simpler; plain old numbers as INT only, but I still cannot get the formatting to work [for pasted values].

it seems like a kluge to me, but I even tried a macro to apply F2 + Enter to any range of cells, but that does not seem to work. It must be manually done.

I am at a loss now. Is this a bug in Excel 2013 with no workaround?