How to identify cells containing formulae with no external references?

**amaroo460** · 09-03-2012, 07:06 PM

In Excel 2003 I want to apply conditional formats to entire worksheets to highlight with colour a) cells with constant numerical values, including formulae with no external references (e.g. a volume entered as a product of 3 numbers); b) cells containing formulae that don't reduce to constant value (i.e. with references to other cells).
I'm 90% there using the function IsNumber and a function created in Virtual Basic:

Please Login or Register  to view this content.

My problem is how do I identify cells with constant value formulae to colour them as a) rather than b)?

**rscsmith** · 09-04-2012, 02:59 AM

Hi Amaroo

Welcome to the forum.

Check out VBA

Please Login or Register  to view this content.

Precedents are the cells that are inputs to a formula.

You will need some error handling, as this function returns error if there are no precedents. I guess the final function will be something like:

Cheers, Rob.

**amaroo460** · 09-04-2012, 04:32 AM

Hi Rob. I had an answer from another website that works nicely:

Please Login or Register  to view this content.

Peter

**Cutter** · 09-04-2012, 10:19 AM

Hello amaroo460, and welcome to the forum.

Unfortunately you have inadvertently broken three of the forum rules. Please read the following. Thanks.

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/search.php?searchid=191552

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

Based on your last post it seems that you have found a solution to your issue but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

**amaroo460** · 09-05-2012, 12:52 AM

Hello Cutter

My apologies for breaking the rules in my first use of the forum - of any such forum in fact. As with all the computer downloads these days, for which you you must agree to the terms, I failed to read the detail. So I missed Rule 8 against cross-postings. It does make sense, and I'll certainly observe it in future, as I very much appreciate the time experts are willing to spend responding to queries, and one does not want to be the cause of that time being wasted.
I would edit the posting as you suggest, except that when I click the "Edit Post" link I get no response. The only other posting I made was to www.mrexcel.com.

Nor did I read Rule 3 explaining the use of code tags - I now understand how code segments appear in their own box.

And finally, I accept the need to mark threads as "Solved". I guess you do this under "Thread Tools" (now showing the option to "Mark this thread as unsolved")

I note the similarity of format of ExcelForum and MrExcel pages, which no doubt indicates some connection. However, I don't see any "Edit post" link there (despite having edit permission", nor any way to mark the post there as Solved.

I do appreciate the very polite way in which you drew these matters to my attention. I am extremely grateful for the time that you and so many others put into the service - and amazed that I got such a quick solution to my problem.

amaroo460