Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
=ISFUNCTION
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
=ISFUNCTION
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. "Neil Pearce" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
=ISFUNCTION
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/Writin...ionsInVBA.aspx -- Gary''s Student - gsnu200909 "Neil Pearce" wrote: 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. "JLatham" wrote: 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. "Neil Pearce" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
=ISFUNCTION
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!* "Neil Pearce" wrote: 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. "JLatham" wrote: 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. "Neil Pearce" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
=ISFUNCTION
On Tue, 1 Dec 2009 05:05:01 -0800, Neil Pearce
wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
=ISFUNCTION
For my own curiousity...
Why mark the function volatile? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Gary''s Student" wrote: 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/Writin...ionsInVBA.aspx -- Gary''s Student - gsnu200909 "Neil Pearce" wrote: 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. "JLatham" wrote: 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. "Neil Pearce" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|