Is there a eqn to determine if a formula or number is in a c

[Brian]

Is there an equation to determine if a formula or number is in a cell?

I want to conditional format my worksheet and turn all of the formulas blue, so that I can tell if someone overwrites a formula.

Thanks,

Brian

[Adcb]

This has been covered before.
Try doing a search.

[Yogi Anand]

Hi Brian:
You may want to try the TYPE Function

=TYPE(A1) ... should give 8 for formula, 1 for number

Regards!

[Brian]

I did do a search, but I'll try again.

I tried the TYPE function, but it did not work (ie. returned 1 (for number)) when pointed to cells with the formula:
=2+3
and
=offset(...)

So I created my own VBA function (re another post that was solved), but I though excel would have a built-in function.

Brian

[Joe Was]

This is a MsgBox Utility I use when designing Sheets. With one hot-key it gives most of the cell selection data you need to design or debug a cell. It will tell you the cell: format, alignment, type, size, ect. I have found that formulas or code may not work or display right if your cell is of a format different then your formula or code wants, this utility will tell you "what you have" with one hot-key. When formating rows or columns in different parts of your sheet it saves you from going to several pull-downs for information. You can also see how I coded the info to help you code your own. Hope it helps. JSW

Sub myCDType()
'Cell type(Data, Format & Formula test), of single cell in MsgBox.
'Load code in "Module."
'Best if run by Hot-key(Macros-Macro-Options).
'
'Test for formula in cell.
Dim myF As String
If Selection.HasFormula = True Then myF = " and contains a [Formula]." Else _
myF = " and contains [No Formula]."
'Test Alignment of selection?
If Selection.HorizontalAlignment = xlGeneral Then X = "General" Else
If Selection.HorizontalAlignment = xlLeft Then X = "Left" Else
If Selection.HorizontalAlignment = xlCenter Then X = "Center" Else
If Selection.HorizontalAlignment = xlRight Then X = "Right" Else
If Selection.HorizontalAlignment = xlFill Then X = "Fill" Else
If Selection.HorizontalAlignment = xlJustify Then X = "Justify" Else
If Selection.HorizontalAlignment = xlCenterAcrossSelection Then X = "Center Across Selection" Else
If Selection.HorizontalAlignment = xlDistributed Then X = "Distributed" Else
If Selection.VerticalAlignment = xlTop Then Y = "Top" Else
If Selection.VerticalAlignment = xlCenter Then Y = "Center" Else
If Selection.VerticalAlignment = xlBottom Then Y = "Bottom" Else
If Selection.VerticalAlignment = xlJustify Then Y = "Justify" Else
If Selection.VerticalAlignment = xlDistributed Then Y = "Distributed" Else
Select Case True
'Test cell data type & format, return to MsgBox for display.
'Note: For some reason the case-order is important!
'
'Cell is Blank, Cell Format & Formula?
Case IsEmpty(Selection): MsgBox "The Cell Data is: [Blank] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Text, Cell Format & Formula?
Case Application.IsText(Selection): MsgBox "The Cell Data is: [Text] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Date, Cell Format & Formula?
Case IsDate(Selection): MsgBox "The Cell Data is a: [Date] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Time, Cell Format & Formula?
Case InStr(1, Selection.Text, ":") <> 0: MsgBox "The Cell Data is a: [Time] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Logical, Cell Format & Formula?
Case Application.IsLogical(Selection): MsgBox "The Cell Data is: [Logical] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Error, Cell Format & Formula?
Case Application.IsErr(Selection): MsgBox "The Cell Data is an: [Error] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Value, Cell Format & Formula?
Case IsNumeric(Selection): MsgBox "The Cell Data is a: [Value] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
End Select
End Sub