Conditional formating: "is it a formula or not ?"

[Mer1in77]

Hi all,

I was wondering if it is possible to somehow use Conditional Formatting to make the text color of whatever is in that cell, say, blue, based on if the value I'm looking at is the result of a formula or if it was entered by hand by a user.

Thanks in advance for all your help guys...

 

[Richard Schollar]

Hi & Welcome to the Board!

You can distinguish between a formula cell and a non formula cell using an old XLM4 macro function:

1. Select cell A1
2.From xl, go Insert>Name>Define and define a Name called "HasFormula"
3. In the refers to box type:
=GET.CELL(48,!A1)
4. Select the area you want the CF to apply to and go Format>ConditionalFormatting
5. Change to Formula Is and use:
=HasFormula
6. Apply desired format.

 

[RoryA]

1. Select cell A1.
2. Choose Insert->Name->Define from the menu.
3. Enter HasFormula in the Names in workbook box, then in the refersto box enter:

=GET.CELL(48,!A1)

4. Then select the range you want highlighted, choose Format->Conditional Formatting, change the dropdown to FormulaIs and enter =HasFormula and choose the formatting you require.
HTH

 

[RoryA]

Now that's a bit spooky!

 

[Richard Schollar]

Spooky is the word!

 

[Lewiy]

Richard, I seem to remember sometime last year, there was a thread that listed a whole load of XLM4 macros. Do you happen to have the link to this? Or know where I can find a list of them?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Cheers

 

[Richard Schollar]

Hi Lewiy

Is it this one:

http://www.mrexcel.com/forum/showthread.php?t=20611

 

[Lewiy]

Yeah, that looks like it. Cheers again!

 

[Mer1in77]

Ok I suspect something isn't going quite right.
When entering GET.CELL(48,!A1) in the refers to box and pressing ok all seems fine.
Then after doing the rest no color change can be seen.
After checking I found that excel has changed it to GET.CELL(48,!A65496)

Now since I like to know what I'm doing, I google'ed that function and found that the 48 is an ID that tells excel what to do in that cell and the rest is some kind of range.

My question is (ofcourse) what's that range got to do with anything ?
Why has excel changed what I input, and what does that exclamation mark do ?



PS: I hate the fact that excel translates formula names ever tried working in three languages in excel ? it's NUTS !

 

[Norie]

Don't use !A1.

Use what Tom posted here.

Which I just noticed is very similar to what's in the link Richard posted.