Find and Replace locates some colors but not others in Excel


In some cases, when attempting to locate cells with various font or fill colors in Excel (2007), the Find and Replace function returns a message saying:

"Microsoft Office Excel cannot find the data you're searching for."

even though the color specified in the Find function is obviously present.

For example:  the attached file contains two groups of cells each of which has been filled with a color from the default color palette (Figs. 1 & 2).
 1 2
After selecting the option to search for cells filled with one of the colors (tan) in the Find and Replace function (Fig. 3),
clicking Find All successfully determines that nine cells are filled with that color (Fig. 4).
However, when the process is repeated after designating the second color (green) (Fig. 5),
pressing Find All results in an alert indicating that none of those cells can be found (Fig. 6).
Can anyone explain why this is occurring and more importantly, how to fix it?  Why would one color from the default palette be identified and the other not?

Thanks EE-2011.0226.xlsx
Who is Participating?
dlmilleConnect With a Mentor Commented:
Well, I may not know all circumstances that cause this problem and Steve, looks like you've found a couple causal factors.  Could be an installation issue or some support files out of sync (just a guess here, as I didn't find anything googling on this problem after about 5 minutes of searching).

However, I have what I believe (without exhaustive testing) is a fix for the problem (doesn't prevent future ones, as we aren't sure exactly what caused it, your observations notwithstanding).  Run the Macro called "FixAllColorsInSheet()" on the active sheet (all it does is set cell color equal to cell color, for the USED RANGE).  If this works, you can wrap this to hit all sheets in a workbook.  I just check in the loop to see if there is a fill color and if there is, just set its value to its value - it seemed to clear it up in your posted sample.

so, in the attached, try to replace the green cells using the replace using format.  You should be getting the error it can't find any.  THEN run my simple macro and try again.


What happens if click "find format from cells" and then point on you target cell?
The colors you selected are not from the standard,  i guess.
Steve_BradyAuthor Commented:
>>What happens if click "find format from cells"

Hmm...  I've never used that before or even -- I hate to admit -- noticed it.

I tried it by pointing to the first green cell (B4) and then clicked Find All.  It found six cells which I determined were all those which are both green and General formatted (i.e. it did not find D4 which is Date formatted or I4 and J4 which are currency formatted).  So, that's a step in the right direction!

Now the question is how to get it to find all green cells without regard to the formatting under the Number tab in the Format Cells box.  Any suggestions?

    ^^^^^^^^^    Time Out for Testing    ^^^^^^^^^    

OK, now this is really odd.  After writing the above paragraph, I did some experimenting and it seems that the problem is not so much the color as it is the method used to fill the cell(s) in the first place combined with the method used to specify the search color:

I filled some cells using the paint bucket from my Quick Access Toolbar (QAT) and then filled other cells by going to Format Cells > Fill tab.  In both cases I used the same colors which were chosen only from the default color palette (i.e. I did not click the More Colors... button).

Next I used the "Choose Format From Cell..." option in Find box > Format drop down to specify the search color and then repeated the process by using Find > Format > Format Cells > Fill.

Amazingly, the results were all over the place:  some times only certain colors were found and in other cases others were found.  I'm not going to take the time to include the different results but it would be great if someone could explain why.


BTW, thanks for the tip on using the "Choose Format From Cell..." option.

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I agree that they are not.  perhaps when they were colored, a shade off was selected.  I opened up your reference sheet and experienced the same thing.

Then, I looked at the RGB Codes for each of the greens - see attached.  I ran three tests - interior.color,interior.colorindex, and showRGBColor.  They are the SAME COLOR

I do not believe this has anything to do with standard or nonstandard pallet, because you can do a find on a selected cell's format - and even that doesn't change all of them on a replace.  However, the color is in my standard pallet. and I checked the RGB code with my pallet color and its the same.  I even tried the RGB code as the source - see image, and that didn't work.  I'm just as puzzled but perhaps we have a place from which to start.
 demo RGBNot a solution - but info we don't have to necessarily repeat as E-E experts look into this...

See attached,

Steve - does this work for you, and do you need it to work on an entire workbook?

For non-standard colors you need to use the RBG values rather than selecting the color from the color palatte directly. You can try this set the color of K3 to the same color as J3 by selecting it from the color palette. Once you try to find that color then you will not get K3 in the rang of matching. But set it by the RGB and find by RGB and you will be able to get it in the result of finding.

"Find" seems to work fine for the standard colors when selected directly from the color palette. Other than the standards colors you need to set the cell's color by using "More colors and add RGB values" and when looking you set the colors format (Find window) by using the RGB as well, and only then you have match.

When I opened your file in Excel 2003, both colors are not available there, instead they will be replaced by the nearest standard colors and when tried to find by selecting the showing color, cells were found as expected.

It seems to be that the color palette at the main tools bar assigns different colors (different ratio) for the non-standard colors than the color palette at the "find" format window. That can be the only reason for colors not match up. But this should be investigated further.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.