Find and Replace locates some colors but not others in Excel

Posted on 2011-02-26
Last Modified: 2012-05-11

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
Question by:Steve_Brady
  • 3
  • 2
LVL 24

Expert Comment

ID: 34990109
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.

Author Comment

ID: 34990314
>>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.

LVL 42

Expert Comment

ID: 34990337
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,

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

LVL 42

Accepted Solution

dlmille earned 500 total points
ID: 34990362
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.


LVL 42

Expert Comment

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

LVL 24

Expert Comment

ID: 35003247
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.

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Compare Excel spreadsheet question. 2 24
Excel vba question 7 44
VBA: copy range dynamically based on config sheet v3 6 32
Excel Formula to Iterate 4 12
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question