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
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|