CF to distinguish Formulas from Inputs

[Brian Ballek]

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

 

[PCLIVE]

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

Sub test()

For Each cell In Range("D67")
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

 

[Gord Dibben]

Brian

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

CF>Formula is: =IsFormula(cellref)


Gord Dibben MS Excel MVP

 

[T. Valko]

Here's a non-VBA method

Create this named formula:

Goto Insert>Name>Define
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 Format>Conditional Formatting
Formula Is: =IsFormula
Click the Format button
Select the style(s) desired
OK out

 

[Brian Ballek]

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]

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

Insert>Name>Define
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.

 

[Max]

..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

---

 

[Ragdyer]

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.