 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2007Conditional formatting
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 You would need to build a simple UDF Function IsFormula(rng as range) IsFormula = rng.HasFormula End Function and use that within your CF  SignatureHTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi, > [quoted text clipped - 10 lines] > > 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." SignatureEarl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- > You would need to build a simple UDF > [quoted text clipped - 18 lines] >> >> DonH 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 > Bob, > [quoted text clipped - 26 lines] > >> > >> DonH 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. SignatureEarl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- > Earl, > [quoted text clipped - 42 lines] >> >> >> >> DonH > Bob, > [quoted text clipped - 39 lines] > > - 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 > You would need to build a simple UDF > [quoted text clipped - 27 lines] > > - Show quoted text - Thanks Bob |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.