Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Find and Replace locates some colors but not others in Excel

Posted on 2011-02-26
6
Medium Priority
?
410 Views
Last Modified: 2012-05-11
Hello,

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),
 3
clicking Find All successfully determines that nine cells are filled with that color (Fig. 4).
 4
However, when the process is repeated after designating the second color (green) (Fig. 5),
 5
pressing Find All results in an alert indicating that none of those cells can be found (Fig. 6).
 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
0
Comment
Question by:Steve_Brady
  • 3
  • 2
6 Comments
 
LVL 24

Expert Comment

by:jimyX
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.
0
 

Author Comment

by:Steve_Brady
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.

Thanks

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



0
 
LVL 42

Expert Comment

by:dlmille
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,

Dave
GetRGB.xlsm
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 42

Accepted Solution

by:
dlmille earned 2000 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.

Cheers,

Dave
GetRGB-r2.xlsm
0
 
LVL 42

Expert Comment

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

Dave
0
 
LVL 24

Expert Comment

by:jimyX
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.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

782 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