Solved

Find and Replace locates some colors but not others in Excel

Posted on 2011-02-26
6
402 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 41

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 41

Accepted Solution

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

Cheers,

Dave
GetRGB-r2.xlsm
0
 
LVL 41

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

747 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

12 Experts available now in Live!

Get 1:1 Help Now