Conditional formatting

[donh]

Hi,

The worksheet I've been working on uses paste links to carry forward
workers details from one worksheet to the next. These cells are left
unprotected so the chain can be broken if a person leaves, and someone
new has to be inserted.

This is fine but it makes it tricky to spot where breaks occur. Can
conditional formatting tell the difference between a formula and text
entered? If so can someone please give me an example that I can use.

Many thanks.

DonH

 

[Bob Phillips]

You would need to build a simple UDF

Function IsFormula(rng as range)
IsFormula = rng.HasFormula
End Function

and use that within your CF

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Earl Kiosterud]

Bob,

Using a UDF in conditional formatting is fraught with peril. It's another
Excel gotcha. I did it once. The UDF worked perfectly in the conditional
formatting, but I spent hours down the road trying to figure out why strange
stuff was intermittently appearing on the screen. I found it in the
Knowledge Base only after I'd figured out that it was the UDF in conditional
formatting that was the culprit. The fix they gave was "don't do that."

 

[donh]

You would need to build a simple UDF

Function IsFormula(rng as range)
IsFormula = rng.HasFormula
End Function

and use that within your CF

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)










- Show quoted text -

Thanks Bob

 

[Bob Phillips]

Earl,

I have heard anecdotal stuff frequently about UDFs in CF, but I have never
had a problem (at least not one that I know about).

Can you elaborate on the gotcha that you experienced and point to that
article?

Thanks

Bob

 

[Earl Kiosterud]

Bob,

I wrote a VBA function, and used in in a formula in conditional formatting.
The conditional formatting worked as designed, but put strange things on the
screen that wouldn't go away. I don't remember exactly what they looked
like. I'd have to fire up the old machine, I think, to attempt to retrieve
it, and I don't remember which project it was. The problem was
intermittent, and as I developed the project further, it got intolerable. I
searched the knowledge base, but wasn't sure what to look for. I started
running progessively older copies of the workbook, and fortunately, it
failed enough that it didn't take too long to find the newest one that
worked correctly. I'd added the UDF in CF in the first version that failed.
At that point I searched for "conditional format user defined function" and
found Q213243.

The whole thing cost me hours. I wound up not using the CF, but just doing
the formatting in code.

 

[donh]

Bob,

Using a UDF in conditional formatting is fraught with peril. It's another
Excel gotcha. I did it once. The UDF worked perfectly in the conditional
formatting, but I spent hours down the road trying to figure out why strange
stuff was intermittently appearing on the screen. I found it in the
Knowledge Base only after I'd figured out that it was the UDF in conditional
formatting that was the culprit. The fix they gave was "don't do that."
--
Earl Kiosterudwww.smokeylake.com








- Show quoted text -

If this causes a problem is there a way around this so I can still
identify a cell if the formula has been deleted?

Many thanks

DonH