CF to distinguish Formulas from Inputs
 

Hi All,

I have been trying (unsuccessfully) to have Excel format cells with
values that are inputs (i.e. directly entered and NOT the result of
formulas) so that I can quickly distinguish actual values from the
formulas used to project the trends of these values into the future. But
Excel seems to see both types of cells as values regardless of whether
the value was directly entered or derived from a formula.

I'd be happy for any solution that will work, whether CF, user-defined
number format, or VBA. I've tried all 3 and haven't cracked it but my
knowledge of VBA is pretty limited.

Many thanks!
Brian Ballek

CF to distinguish Formulas from Inputs
 

Here's one possible way you could test if the cell is a formula or not.

Sub test()

For Each cell In Range("D6:D7")
If Left(cell.Formula, 1) = "=" _
Then
MsgBox ("Cell " & cell.Address & " is a Formula.")
Else
MsgBox ("Cell " & cell.Address & " is Not a Formula.")
End If
Next cell

End Sub

HTH,
Paul


--

"Brian Ballek" wrote in message
...
Hi All,

I have been trying (unsuccessfully) to have Excel format cells with values
that are inputs (i.e. directly entered and NOT the result of formulas) so
that I can quickly distinguish actual values from the formulas used to
project the trends of these values into the future. But Excel seems to see
both types of cells as values regardless of whether the value was directly
entered or derived from a formula.

I'd be happy for any solution that will work, whether CF, user-defined
number format, or VBA. I've tried all 3 and haven't cracked it but my
knowledge of VBA is pretty limited.

Many thanks!
Brian Ballek

CF to distinguish Formulas from Inputs
 

Brian

Function IsFormula(cell)
IsFormula = cell.HasFormula
End Function

CFFormula is: =IsFormula(cellref)


Gord Dibben MS Excel MVP

On Mon, 06 Aug 2007 22:57:29 +0200, Brian Ballek
wrote:

Hi All,

I have been trying (unsuccessfully) to have Excel format cells with
values that are inputs (i.e. directly entered and NOT the result of
formulas) so that I can quickly distinguish actual values from the
formulas used to project the trends of these values into the future. But
Excel seems to see both types of cells as values regardless of whether
the value was directly entered or derived from a formula.

I'd be happy for any solution that will work, whether CF, user-defined
number format, or VBA. I've tried all 3 and haven't cracked it but my
knowledge of VBA is pretty limited.

Many thanks!
Brian Ballek

CF to distinguish Formulas from Inputs
 

Here's a non-VBA method

Create this named formula:

Goto InsertNameDefine
Name: IsFormula
Refers to: =GET.CELL(48,INDIRECT("RC",FALSE))
OK

Apply conditional formatting to the cells in question.
Assume this range of cells is A1:A10
Select the range A1:A10
Goto FormatConditional Formatting
Formula Is: =IsFormula
Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


"Brian Ballek" wrote in message
...
Hi All,

I have been trying (unsuccessfully) to have Excel format cells with values
that are inputs (i.e. directly entered and NOT the result of formulas) so
that I can quickly distinguish actual values from the formulas used to
project the trends of these values into the future. But Excel seems to see
both types of cells as values regardless of whether the value was directly
entered or derived from a formula.

I'd be happy for any solution that will work, whether CF, user-defined
number format, or VBA. I've tried all 3 and haven't cracked it but my
knowledge of VBA is pretty limited.

Many thanks!
Brian Ballek

CF to distinguish Formulas from Inputs
 

Yup, that did it - Fantastic! What I actually wanted was to highlight
values that *aren't* from formulas (so all the variables in a sheet are
clearly seen) but for that I just modified the CF to

=IsFormula=False

THANKS!

T. Valko wrote:
Here's a non-VBA method

Create this named formula:

Goto InsertNameDefine
Name: IsFormula
Refers to: =GET.CELL(48,INDIRECT("RC",FALSE))
OK

Apply conditional formatting to the cells in question.
Assume this range of cells is A1:A10
Select the range A1:A10
Goto FormatConditional Formatting
Formula Is: =IsFormula
Click the Format button
Select the style(s) desired
OK out

CF to distinguish Formulas from Inputs
 

Ok, that'll work but it's using "reverse" logic. How about this using
"straight" logic...

InsertNameDefine
Name: IsConstant
Refers to: =NOT(GET.CELL(48,INDIRECT("RC",FALSE)))

Then

Again, assuming the range of interest is A1:A10
Conditional Formatting
Formula Is: =AND(A1<"",IsConstant)

The addition of AND(A1<"" keeps empty cells from being highlighted.

--
Biff
Microsoft Excel MVP


"Brian Ballek" wrote in message
...
Yup, that did it - Fantastic! What I actually wanted was to highlight
values that *aren't* from formulas (so all the variables in a sheet are
clearly seen) but for that I just modified the CF to

=IsFormula=False

THANKS!

T. Valko wrote:
Here's a non-VBA method

Create this named formula:

Goto InsertNameDefine
Name: IsFormula
Refers to: =GET.CELL(48,INDIRECT("RC",FALSE))
OK

Apply conditional formatting to the cells in question.
Assume this range of cells is A1:A10
Select the range A1:A10
Goto FormatConditional Formatting
Formula Is: =IsFormula
Click the Format button
Select the style(s) desired
OK out

CF to distinguish Formulas from Inputs
 

"Brian Ballek" wrote:
..What I actually wanted was to highlight values that *aren't* from
formulas (so all the variables in a sheet are clearly seen)

Laterally thinking ..

Press F5 Special Check "Constants" OK
selects all non-formula cells in the sheet

Press F5 Special Check "Formulas" OK
selects all formula cells in the sheet

With the above selections done in a flash, you could then easily Format
Cells to taste
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

CF to distinguish Formulas from Inputs
 

I believe that it might not be a bad idea to always include a warning
whenever suggesting the use of these 4.0 macros.

Caveat:
This can safely be used in versions of XL, from XL02 onward.

Earlier versions *WILL* CRASH*, causing the loss of all unsaved work, when
attempting to copy these formulas to other WBs.
Can be used safely in earlier versions as long as copying is restricted to
sheets within the existing WB.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"T. Valko" wrote in message
...
Ok, that'll work but it's using "reverse" logic. How about this using
"straight" logic...

InsertNameDefine
Name: IsConstant
Refers to: =NOT(GET.CELL(48,INDIRECT("RC",FALSE)))

Then

Again, assuming the range of interest is A1:A10
Conditional Formatting
Formula Is: =AND(A1<"",IsConstant)

The addition of AND(A1<"" keeps empty cells from being highlighted.

--
Biff
Microsoft Excel MVP


"Brian Ballek" wrote in message
...
Yup, that did it - Fantastic! What I actually wanted was to highlight
values that *aren't* from formulas (so all the variables in a sheet are
clearly seen) but for that I just modified the CF to

=IsFormula=False

THANKS!

T. Valko wrote:
Here's a non-VBA method

Create this named formula:

Goto InsertNameDefine
Name: IsFormula
Refers to: =GET.CELL(48,INDIRECT("RC",FALSE))
OK

Apply conditional formatting to the cells in question.
Assume this range of cells is A1:A10
Select the range A1:A10
Goto FormatConditional Formatting
Formula Is: =IsFormula
Click the Format button
Select the style(s) desired
OK out