Colour cell if cell contains a formula (non VBA method)

[Grizlore]

Dear All,

I think this can be done by using VBA but i was wondering if there was a way to do this without.

I would like to highlight (with Conditional formatting) if a cell contains a formula. Or if easier, colour a cell if it doesnt contain a formula.

Would anyone know if this is possible and what formula to use in the conditional format formula field to do this please?


Thanks in anticapation

Regards

 

[JackDanIce]

I don't think it can be done without use of VBA or at least writing your own custom function as there is no Excel function that does this (as far as I know).

One that I use is:

Function IsFormula(c As Range) As Boolean
IsFormula = c.HasFormula
End Function

And then apply a conditional format to the output

 

[gsbelbin]

I can't see how this could be done but if it's just a one off colouring of the cells that you want to do then you could do this by using Edit, Go To, Special, Constants. This will select all the non-formula cells which you can then colour as you wish.

 

[Jon von der Heyden]

For the record I would go with a VBA approach, either a UDF as Jack has supplied or a routine to colour all constants or formulae as gsbelbin has suggested (in VB this suggestion uses the SpecialCells method).

As far as non VBA solutions go one way will be to use a XLM4 function. These have to be applied in names and then the name is called to get the result (either in a cell formula or conditional formatting).

So let's say you want to test if A1 has a formula. Go to a cell that can be used to flag whether or not A1 has a formula (i.e. use a helper column, so e.g. if column Z is available go to Z1)

Create a new name called 'HasFormula'
Refers to: =GET.CELL(48,!A1)

Now in Z1 type: =HasFormula

This will yield TRUE if it does, else FALSE. Alternatively you can call the name from within Conditional Formatting.

 

[SteveO59L]

Similar post earlier this morning

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

 

[Grizlore]

Thanks everyone.

Jon - your non VBA solution works a treat.

All your help is much appreciated !

 

[Jon von der Heyden]

Nice spot Steve.

Taking a link from that referral, JW seems to have quite a neat tutorial here: http://www.j-walk.com/ss/excel/usertips/tip045.htm

EDIT

Thanks everyone.

Jon - your non VBA solution works a treat.

All your help is much appreciated !

Ah seems you got it working. Well done.

 

[Grizlore]

possibly a silly question, and therefore should be an easy answer

How do I amend this so that it highlights cell WITHOUT a formula?

I have tried changing False to True

= to <>


Feel free to correct me and laugh at my foolishness

 

[pgc01]

Hi

try:

=NOT(HasFormula)

 

[Grizlore]

That's done it.. many thanks.

It's been a long day!