=ISFUNCTION

[Neil Pearce]

All,

I am aware of the IS forumlae. But how would I test if a cell is a function
that calucates a number as opposed to a hard inputted number? The end goal
being to highlight any cells where a formula is overwritten using conditonal
formatting.

The forumla in question being:

=IF(NOT(B9=""),VLOOKUP(B9,'Project Overview'!$A$34:$E$52,COLUMNS('Project
Overview'!$A$33:$E$33),FALSE),0)


Appreciated as always,

Neil

 

[JLatham]

Edit --> Go To --> [Special] and choose Formulas and the sub option(s) in
that list for the type of results, Number, text, etc, that you are looking
for.

 

[Luke M]

You could insert this short UDF into a module in the Visual Basic Editor, and
then call the formula IsFunction to check for a formula.

'========
Function IsFunction(TestCell As Range) As Boolean
If Left(TestCell.Formula, 1) = "=" Then
IsFunction = True
Else
IsFunction = False
End If
End Function
'========
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Hi there,

Thank you.

That is great for locating those cells which contain a formula. However I
am hoping to create a formula such that conditional formatting will highlight
a cell if that cell no longer caontinas a formula but is instead overwriten
with a hard number by a user.

e.g. NOT(ISFORMULA(A1)) then fill cell yellow.

Edit --> Go To --> [Special] and choose Formulas and the sub option(s) in
that list for the type of results, Number, text, etc, that you are looking
for.

All,

I am aware of the IS forumlae. But how would I test if a cell is a function
that calucates a number as opposed to a hard inputted number? The end goal
being to highlight any cells where a formula is overwritten using conditonal
formatting.

The forumla in question being:

=IF(NOT(B9=""),VLOOKUP(B9,'Project Overview'!$A$34:$E$52,COLUMNS('Project
Overview'!$A$33:$E$33),FALSE),0)


Appreciated as always,

Neil

 

[Gary''s Student]

Try this small UDF:

Public Function IsFormula(r As Range) As Boolean
Application.Volatile
IsFormula = r.HasFormula
End Function


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

--
Gary''s Student - gsnu200909

Hi there,

Thank you.

That is great for locating those cells which contain a formula. However I
am hoping to create a formula such that conditional formatting will highlight
a cell if that cell no longer caontinas a formula but is instead overwriten
with a hard number by a user.

e.g. NOT(ISFORMULA(A1)) then fill cell yellow.

Edit --> Go To --> [Special] and choose Formulas and the sub option(s) in
that list for the type of results, Number, text, etc, that you are looking
for.

All,

I am aware of the IS forumlae. But how would I test if a cell is a function
that calucates a number as opposed to a hard inputted number? The end goal
being to highlight any cells where a formula is overwritten using conditonal
formatting.

The forumla in question being:

=IF(NOT(B9=""),VLOOKUP(B9,'Project Overview'!$A$34:$E$52,COLUMNS('Project
Overview'!$A$33:$E$33),FALSE),0)


Appreciated as always,

Neil

 

[Ron Rosenfeld]

All,

I am aware of the IS forumlae. But how would I test if a cell is a function
that calucates a number as opposed to a hard inputted number? The end goal
being to highlight any cells where a formula is overwritten using conditonal
formatting.

The forumla in question being:

=IF(NOT(B9=""),VLOOKUP(B9,'Project Overview'!$A$34:$E$52,COLUMNS('Project
Overview'!$A$33:$E$33),FALSE),0)


Appreciated as always,

Neil

I'm not sure exactly what you mean, but here are some considerations.

1. There is no native Excel function to detect formulas.
2. VBA contains tools to detect if a formula exists in a cell.
3. Conditional Formatting must refer to cells on the worksheets. In other
words, it cannot refer to a UDF.

4. If you want to use Conditional Formatting (which would be dynamic), you
could set up a cell that tests for the presence of a formula (using a UDF) in
some hidden column, and then use that cell to conditionally format the cell of
interest. For example, if you wanted to highlight cells in the range
$C$5:$C$14, you could use the IsFormula() UDF (see below) entered into, let us
say, G5:G14.

G5: =IsFormula(C5)

Then fill down to G14

Then, for conditional formatting, use this:

Select C5:C14
Formula Is: =$G5=TRUE
Format to taste

If it doesn't seem to work initially, make sure your addressing mode is
correct, and that any quotes outside the formula are eliminated.

5. You could also use a VBA Macro (sub) to highlight cells with formulas. This
would not be dynamic, however.

------------------------
To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

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

------------------------------------------
Or, for a Macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.

========================================
Option Explicit
Sub HighLightFormulas()
Dim c As Range
Set c = Cells.SpecialCells(xlCellTypeFormulas)
c.Interior.Color = vbYellow
End Sub
========================================
--ron

 

[Luke M]

For my own curiousity...
Why mark the function volatile?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Try this small UDF:

Public Function IsFormula(r As Range) As Boolean
Application.Volatile
IsFormula = r.HasFormula
End Function


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

--
Gary''s Student - gsnu200909

Hi there,

Thank you.

That is great for locating those cells which contain a formula. However I
am hoping to create a formula such that conditional formatting will highlight
a cell if that cell no longer caontinas a formula but is instead overwriten
with a hard number by a user.

e.g. NOT(ISFORMULA(A1)) then fill cell yellow.

Edit --> Go To --> [Special] and choose Formulas and the sub option(s) in
that list for the type of results, Number, text, etc, that you are looking
for.

:

All,

I am aware of the IS forumlae. But how would I test if a cell is a function
that calucates a number as opposed to a hard inputted number? The end goal
being to highlight any cells where a formula is overwritten using conditonal
formatting.

The forumla in question being:

=IF(NOT(B9=""),VLOOKUP(B9,'Project Overview'!$A$34:$E$52,COLUMNS('Project
Overview'!$A$33:$E$33),FALSE),0)


Appreciated as always,

Neil