In Excel 2002, I could use the Excel find/replace options to replace any value in a cell based on it's formatting. It was useful for when someone sent me a spreadsheet where they had marked up some of the cells by colouring them in, but which I needed to import into Access (which obviously doesn't import the cell formatting)



I could use the 'choose format from cell' option in the find/replace box, leave the 'Find What' box empty and update the cell. For example, I could update any cell that was formatted to have a yellow background with the word 'Yellow'. That way I could import into Access and see which cells had been marked up because Access would show me the text 'Yellow' in that cell.

In Excel 2013, the same functionality appears to exist, but the problem is that Excel refuses to find the cell based on the format - it can't even find the cell from which I chose the format. I just keep getting the 'We couldn't find what you are looking for message' whenever I try it.

Am I missing something obvious?!!

Cheers

OK, found a way around this - use home menu/sort and filter button and choose filter. From the top line, choose Filter by Color and select the required colour. Then mark up the filtered records by putting in some text (I use 'x') and dragging down so it populates all the lines in the filter. Then toggle the filter off again. The data can now be imported into a program that does not support cell colours and you have the 'x' to show which records were marked up.

This is a decent workaround for me, but I'm still curious as to why find/replace by format no longer seems to work. Does anyone have an answer for this?

Cheers