Finding Cells that Use Conditional Formatting

The Tip has an easy and nice way to select all the cells that have Conditional Formatting ("CF"). No doubt. One interesting thought I can think of for it would be when one wonders if, and/or how much/badly, a CF's rule/s have been Balkanized by things like Copy and Paste: Do this and move about until the range in question is on the screen, then see any "holes" that exist by seeing that they are not selected/highlighted.

It would be a "black and white" visual marking of precisely the same sort people often exactly use CF for, so it's kind of thematic as well. One might even select the range that should end up completely highlighted after the Go To and run it. That way the only cells looked through and selected by the Go To will be the expected range so there'd be no need to navigate through the possibly long list of cells, rows, columns, areas, etc. that might be selected in a full sheet Go To in order to get to the range of interest.

But... if one wants something useful in other ways, or perhaps more granular, one can simply go to the CF manager through the Ribbon (stupid name for what's just a menu) and choose Manage Rules. Once there, use the "Show formatting rules for:" box to select "This Worksheet" and lo and behold, all the rules on the worksheet will be in the region below to scroll up and down through. (To... manage them too...) Notice that all the ranges they apply to are shown and in fact can be Copied and Pasted elsewhere. Locate the ones of interest to you and copy them out. (Before starting just open a new spreadsheet and put it on the other monitor, or window Excel up so you can display enough of it to paste any ranges of interest into it, one after the other, along with any notes you could use to identify them. Then close the CF manager and either work from the note-taking throwaway spreadsheet with the ranges and notes, or Copy and Paste them into the real spreadsheet for reference and close the throwaway.

Lordy, or even do screenshots to collect images of all the rules and the ranges for them. Might miss some material if reallllly Balkanized, so the Copy and Paste method above would be better many of the times you do it.

One use might be that you check them over to ensure they are right and then dress them up to be the minimal, most straightforward version possible. So something like " A1:A5,A8;A32,A6,A4:A14,A7 " would become " A1:A32 " for further use. (Yeah, ranges like that actually happen.) There's a trick often handy in cleaning those up that I'll mention in a moment, but the point of it might be that you are going to create a series of Named Ranges for each range you expect could get torn up (Balkanized). The idea being that when needed, instead of trying to repair the ranges you'll just go to the range boxes and type the Named Range name that holds the range for it. Yes, Excel will convert it to a literal range and save it as such, but when it does it will read the address from the Named Range. The changes that tear up a range are nothing to a Named Range, so it adjusts as needed as each such change is made and the resulting address it holds will always be right. (As "always" as anything can be with computers and human programmers.) So you have an EASY way to restore the ranges: far easier than tying to figure out yourself what each should be and typing each in.

The trick I mentioned for cleaning a range up is that Excel will repair many range addresses for you if they are all separated by colons and you enter them as a range to apply a CF rule to. So if you have " A1:C3:D1 " and enter it with an "=", Excel will take the leftmost column and uppermost row to make the left side of the clean range address, and the rightmost column and lowest row to make the right side. In the example, column A and row 1 will make the left side (A1) and column D and row 3 will make the right side (D3) so the result is " A1:D3 ".

Well, the example range I gave has commas in it too, not just colons, so that won't help, will it? Actually, it will. You can Copy and Paste the range into a cell as text (just as it is, no "=" in front of it). Then replace all the commas with colons. Do a Find and Replace even, if you like, rather than typing. (commas and colons ARE small, and we're all getting older, eh?) After doing so, Copy it and either go to a rule it applies to, or make a throwaway rule that it will apply to. Once the rule is in place, go back to it via Manage Rules and delete what's in the "Applies To" box and type "=" then paste your range into it. Go to the dialog's main buttons, bottom right, and pick "Apply". The instant you press that button, the range will clean up.

The range given above will become " A1:A32 "... the "hole" in it, the missing A7 will not stop this process at all. For this kind of purpose that would very usually (yes, even more "usually" than "usually" itself... um... hey, this isn't 10th grade Composition class so...) be your desired result. Missing rows suddenly back in the fold, that kind of thing. But bear in mind that it might instead be very wrong for you use. So it can't be used for every purpose under the sun.

But for repairing CF ranges themselves, it's a wonder. You can change the commas in that box, never leave the manager for cell-side work, so quick and easy.

It IS awkward though having to use something like the CF manager as a vehicle when you want something for the cells. Do it, Copy the result, leave and put it into the cell that needs it and you're good. But... did you remember to go back into the CF manager and delete that throwaway CF rule? Yeah. Even if you did rather than get rolling with the work that needed that, it was kind of a hassle and took time to clean up the work area.

But outside CF, it's not really often needed so it shouldn't arise much as a hassle. Or problem, if you left the throwaway there. Something to remember. If I have a several comma one, I take it out to cells so I can use F&R. Easier. One or two commas, not a problem. More? Find and Replace. Work smarter, not harder.

@Henry Kafeman:

I found an interesting subtlety. If I had literally " =A1 " in C1, C2, and some other cells, then did your sequence of actions, Excel highlights from C2 through whatever the last cell I used was.

BUT... if I put the formula in C1, then copied the CELL and pasted it to, say, C2 and a couple other cells below, then did your actions, Excel highlighted all the cells that I did NOT paste it into and left the pasted ones not marked.

It would appear that the functionality is NOT looking for identical contents in the case of a formula, but rather the formula with references adjusted as Excel does when pasting it. So pasting it into C2 gets " =A2 " and since that is the "correct" adjustment Excel considers it to not be a difference even though it would certainly seem so to the other eight billion people in the world.

(I'm sure it's more of a "refers to a cell, same row, two columns to its left" kind of matching, but I prefer the above description for descriptive purposes.)

(Didn't require pasting, of course. Typing " =A5 " into C5 and doing it leaves that one out of the highlighted group as well. So it doesn't even have the possibility of Excel somehow remembering a cell was pasted into. Definitely matching that its formula refers to the same kind of something.)

And it does the same for row difference testing.

Allen

Thank you for the tip.

However when I tried if for "Column Differences" it does not work as expected!

If e.g. in a Column you have some Cells containing "=A1" and some Cells containing "=A$1" then if you select the column, set the Active Cell (using Enter or Tab) to one containing "=A1" the GoTo finds all Cells with values not just ones that are different! But when set to a Cell containing "=A$1" it finds only Cells having a different value as expected!

I would guess the "Rows Differences" would also have this same anomaly but have not tried?

Can you please investigate whether this is a Microsoft Bug (and report it as such) or explain how/why this is supposed to work the way it does?

It could cause users great problems/confusion if trying to find cells with or without "$" for any reason!

Do any of the other "Go To Special" options give unexpected results that users need to be aware of???

Thanks
Henry