Apply Conditional Formatting to Merged Cells ONLY

[hal-ber]

Hi,

I have a range where some cells are merged and some are not. I want to apply to such range a conditional formatting that will only show on merged cells.
I can set two rules with the rule number one having the option "stop if true" checked but I am in trouble in finding an excel function that returns true if a cell is merged (what in VBA would be range.mergecells but without using VBA).

Anyone can help? Thanks.

 

[Tom Urtis]

Really, what does this mean:

"what in VBA would be range.mergecells but without using VBA"



Why are you usng merged cells and why do you want to CF them?


There is no built-in worksheet function that identifies a merged cell.

You *could* use Find to locate merged cells using the Format button on the Find dialog box and selecting merged, but that only finds the cells manually; you'd still need to apply conditional formatting. But it's doable without VBA if that is what you are asking.

 

[hal-ber]

Hi Tom, thanks for your initial answer, I'll try to explain better:

I am using Office 2007 Pro+ and conditional formatting using volatile UDF is giving me a lot of issues (randomly appearing black areas, borders missing and even seldom crashes). Everything works just fine when I remove the UDF from any CF rule and use only Excel built-in functions.

The whole Excel application I designed is making heavy use of merged cells and there is no way I can change this (I worked around all the issues with merged cells, I am just stuck on this now) and, most important, there is no way I can achieve the same without merged cells.

Basically I want to see highlighted duplicate values in real time as I type but on "results" only (that are on merged cells). Cells are merged and unmerged dinamically by the underlying VBA code, so I could use another way of tagging all the cells that become part of a merged range (maybe a custom format that I can later check using the "Cell" function?).

I was just wondering if there is a smarter way. Let's put it this way:
Putting forehead that CF using UDF gives issues (at least if in the "Applies to" range there are one or more merged cells), what is the smarter way to tag a group of cells that I do not want the formatting to be applied to? It has to be cosmetic (e.g. font size, cell format, ismerged) not based on value and color because they change too. The only thing that is 100% sure is that if and only if the cell is merged the CF must apply!