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 41

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,

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

LVL 41

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 41

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now