Excel 2010 Find & Replace Can't Find Error

September 21, 2011 at 14:26:20
Specs: Windows XP
Find and Replace - Excel 2010 "Can't Find"

I have used Find and Replace for years. I am trying to use it with Excel 2010 (my company just upgraded). Every time I try it (with new files, old files, or otherwise) it says it cannot find anything. My worksheets/books are not protected.

I should specify that I am trying to find cells that are green and change them to clear. This used to be so simple!

What do I do?

Thanks for your help!

See More: Excel 2010 Find & Replace Cant Find Error

September 21, 2011 at 17:57:08
What version did you upgrade from?

I have found that while using VBA to set/determine the fill color of cells, some of the ColorIndex numbers didn't match between the 2 versions.

For example, in 2003 I filled C3 with Green, making sure I chose the Green that popped up "Green" when I hovered over the color in the dialog box - in other words, not Sea Green or Dark Green, just Green.

When I ran this code, it returned 10:

Sub WhatColor()
  MsgBox [C3].Interior.ColorIndex
End Sub

When I did the same thing in 2010, again making sure I chose Green, the code returned 14, which tells me that the Green in 2003 is different than the Green in 2010.

In fact, when I saved the 2003 workbook and opened it in 2010, I couldn't find a matching Green in the Find/Replace dialog box.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

September 23, 2011 at 08:26:24
We upgraded from Office 2007 to 2010. I think the issue is perhaps in the program from which I am exporting data.

I am exporting a report (the export is an excel spreadsheet) from a program called ActivInspire. My students use remote devices to choose an answer to a multiple choice question on my smart board.

When I run the report, I get an excel spreadsheet (97-2003 version) that shows me who chose what. The correct answers end up green and the incorrect answers red. I saw that you were able to hover over the color to see what it's actually called, but I was not able to do that in any of the dialogue boxes I opened. I have noticed that since Office 2007, the colors that I'm seeing in my export are not "default" colors. In other words, if I wanted to fill A1 the same color green as is in my export, I'd have to click on "more colors" and find it in the color swatch.

The green that I am trying to find is in the same place on the color swatch in 97 - 2003 and 2007 as it is in 2010. SO...that leads me to believe it's the same green. Your macro confirmed this. They both come back "10". Also, I use the "choose format from cell" button when I'm using find/replace which brings up the dropper. I then click in the cell I want it to find so it can recognize it's exact formatting.

Here's the kicker -- even if I'm not searching for color and I search for those cells that include "A" for example, it's still saying it can't find anything. I have never had a problem in the past. I have made sure the cells aren't locked. I don't know what's going on.

I really appreciate your help!!

Report •
Related Solutions

Ask Question