how can i test whether cell contains keyed constant or a formulae

I would like to use conditional formatting to test whether a cell contains text or a formulae and higlight all cells as they are overtyped.

I have tried using Type() but doesnt recognise difference between sam a number keyed in and a formulae beginning =if(. . . .

Any ideas?

Thanks Anil - It worked and I learnt a bit more.

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

anilsolipuram wrote:
>
> You have to write a userdefined function which can determine whether a
> cell has formula or not
>
> below is that funtion which determines whether a cell has formula or
> not, it takes the cell address as the parameter
>
> Function check_formula(r As Range)
> If r.Formula = "" Then
> check_formula = False
> Else
> check_formula = True
> End If
>
> End Function
>
> You have to paste the the above function in vba module.
>
> to use it in any cell type , =check_formula(b3) ' this will find
> whether cell b3 has formula or not , if formula is there it will return
> true, else false
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

Dave
Will this differentiate between a keyed constant and a formula - the difficulty I have encountered is that the keyed constant is recognised as a formula and therefore result will be true for both ?

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.



Ruthki wrote:
>
> Dave
> Will this differentiate between a keyed constant and a formula - the
> difficulty I have encountered is that the keyed constant is recognised
> as a formula and therefore result will be true for both ?
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.



Ruthki wrote:
>
> Dave
> Will this differentiate between a keyed constant and a formula - the
> difficulty I have encountered is that the keyed constant is recognised
> as a formula and therefore result will be true for both ?
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.



Ruthki wrote:
>
> Dave
> Will this differentiate between a keyed constant and a formula - the
> difficulty I have encountered is that the keyed constant is recognised
> as a formula and therefore result will be true for both ?
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.



Ruthki wrote:
>
> Dave
> Will this differentiate between a keyed constant and a formula - the
> difficulty I have encountered is that the keyed constant is recognised
> as a formula and therefore result will be true for both ?
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.



Ruthki wrote:
>
> Dave
> Will this differentiate between a keyed constant and a formula - the
> difficulty I have encountered is that the keyed constant is recognised
> as a formula and therefore result will be true for both ?
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.



Ruthki wrote:
>
> Dave
> Will this differentiate between a keyed constant and a formula - the
> difficulty I have encountered is that the keyed constant is recognised
> as a formula and therefore result will be true for both ?
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.



Ruthki wrote:
>
> Dave
> Will this differentiate between a keyed constant and a formula - the
> difficulty I have encountered is that the keyed constant is recognised
> as a formula and therefore result will be true for both ?
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.



Ruthki wrote:
>
> Dave
> Will this differentiate between a keyed constant and a formula - the
> difficulty I have encountered is that the keyed constant is recognised
> as a formula and therefore result will be true for both ?
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.



Ruthki wrote:
>
> Dave
> Will this differentiate between a keyed constant and a formula - the
> difficulty I have encountered is that the keyed constant is recognised
> as a formula and therefore result will be true for both ?
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.



Ruthki wrote:
>
> Dave
> Will this differentiate between a keyed constant and a formula - the
> difficulty I have encountered is that the keyed constant is recognised
> as a formula and therefore result will be true for both ?
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.



Ruthki wrote:
>
> Dave
> Will this differentiate between a keyed constant and a formula - the
> difficulty I have encountered is that the keyed constant is recognised
> as a formula and therefore result will be true for both ?
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.



Ruthki wrote:
>
> Dave
> Will this differentiate between a keyed constant and a formula - the
> difficulty I have encountered is that the keyed constant is recognised
> as a formula and therefore result will be true for both ?
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

anilsolipuram wrote:
>
> You have to write a userdefined function which can determine whether a
> cell has formula or not
>
> below is that funtion which determines whether a cell has formula or
> not, it takes the cell address as the parameter
>
> Function check_formula(r As Range)
> If r.Formula = "" Then
> check_formula = False
> Else
> check_formula = True
> End If
>
> End Function
>
> You have to paste the the above function in vba module.
>
> to use it in any cell type , =check_formula(b3) ' this will find
> whether cell b3 has formula or not , if formula is there it will return
> true, else false
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

anilsolipuram wrote:
>
> You have to write a userdefined function which can determine whether a
> cell has formula or not
>
> below is that funtion which determines whether a cell has formula or
> not, it takes the cell address as the parameter
>
> Function check_formula(r As Range)
> If r.Formula = "" Then
> check_formula = False
> Else
> check_formula = True
> End If
>
> End Function
>
> You have to paste the the above function in vba module.
>
> to use it in any cell type , =check_formula(b3) ' this will find
> whether cell b3 has formula or not , if formula is there it will return
> true, else false
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

anilsolipuram wrote:
>
> You have to write a userdefined function which can determine whether a
> cell has formula or not
>
> below is that funtion which determines whether a cell has formula or
> not, it takes the cell address as the parameter
>
> Function check_formula(r As Range)
> If r.Formula = "" Then
> check_formula = False
> Else
> check_formula = True
> End If
>
> End Function
>
> You have to paste the the above function in vba module.
>
> to use it in any cell type , =check_formula(b3) ' this will find
> whether cell b3 has formula or not , if formula is there it will return
> true, else false
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

anilsolipuram wrote:
>
> You have to write a userdefined function which can determine whether a
> cell has formula or not
>
> below is that funtion which determines whether a cell has formula or
> not, it takes the cell address as the parameter
>
> Function check_formula(r As Range)
> If r.Formula = "" Then
> check_formula = False
> Else
> check_formula = True
> End If
>
> End Function
>
> You have to paste the the above function in vba module.
>
> to use it in any cell type , =check_formula(b3) ' this will find
> whether cell b3 has formula or not , if formula is there it will return
> true, else false
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

anilsolipuram wrote:
>
> You have to write a userdefined function which can determine whether a
> cell has formula or not
>
> below is that funtion which determines whether a cell has formula or
> not, it takes the cell address as the parameter
>
> Function check_formula(r As Range)
> If r.Formula = "" Then
> check_formula = False
> Else
> check_formula = True
> End If
>
> End Function
>
> You have to paste the the above function in vba module.
>
> to use it in any cell type , =check_formula(b3) ' this will find
> whether cell b3 has formula or not , if formula is there it will return
> true, else false
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

anilsolipuram wrote:
>
> You have to write a userdefined function which can determine whether a
> cell has formula or not
>
> below is that funtion which determines whether a cell has formula or
> not, it takes the cell address as the parameter
>
> Function check_formula(r As Range)
> If r.Formula = "" Then
> check_formula = False
> Else
> check_formula = True
> End If
>
> End Function
>
> You have to paste the the above function in vba module.
>
> to use it in any cell type , =check_formula(b3) ' this will find
> whether cell b3 has formula or not , if formula is there it will return
> true, else false
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

anilsolipuram wrote:
>
> You have to write a userdefined function which can determine whether a
> cell has formula or not
>
> below is that funtion which determines whether a cell has formula or
> not, it takes the cell address as the parameter
>
> Function check_formula(r As Range)
> If r.Formula = "" Then
> check_formula = False
> Else
> check_formula = True
> End If
>
> End Function
>
> You have to paste the the above function in vba module.
>
> to use it in any cell type , =check_formula(b3) ' this will find
> whether cell b3 has formula or not , if formula is there it will return
> true, else false
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

anilsolipuram wrote:
>
> You have to write a userdefined function which can determine whether a
> cell has formula or not
>
> below is that funtion which determines whether a cell has formula or
> not, it takes the cell address as the parameter
>
> Function check_formula(r As Range)
> If r.Formula = "" Then
> check_formula = False
> Else
> check_formula = True
> End If
>
> End Function
>
> You have to paste the the above function in vba module.
>
> to use it in any cell type , =check_formula(b3) ' this will find
> whether cell b3 has formula or not , if formula is there it will return
> true, else false
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

anilsolipuram wrote:
>
> You have to write a userdefined function which can determine whether a
> cell has formula or not
>
> below is that funtion which determines whether a cell has formula or
> not, it takes the cell address as the parameter
>
> Function check_formula(r As Range)
> If r.Formula = "" Then
> check_formula = False
> Else
> check_formula = True
> End If
>
> End Function
>
> You have to paste the the above function in vba module.
>
> to use it in any cell type , =check_formula(b3) ' this will find
> whether cell b3 has formula or not , if formula is there it will return
> true, else false
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

anilsolipuram wrote:
>
> You have to write a userdefined function which can determine whether a
> cell has formula or not
>
> below is that funtion which determines whether a cell has formula or
> not, it takes the cell address as the parameter
>
> Function check_formula(r As Range)
> If r.Formula = "" Then
> check_formula = False
> Else
> check_formula = True
> End If
>
> End Function
>
> You have to paste the the above function in vba module.
>
> to use it in any cell type , =check_formula(b3) ' this will find
> whether cell b3 has formula or not , if formula is there it will return
> true, else false
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

anilsolipuram wrote:
>
> You have to write a userdefined function which can determine whether a
> cell has formula or not
>
> below is that funtion which determines whether a cell has formula or
> not, it takes the cell address as the parameter
>
> Function check_formula(r As Range)
> If r.Formula = "" Then
> check_formula = False
> Else
> check_formula = True
> End If
>
> End Function
>
> You have to paste the the above function in vba module.
>
> to use it in any cell type , =check_formula(b3) ' this will find
> whether cell b3 has formula or not , if formula is there it will return
> true, else false
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=380978

--

Dave Peterson

Try http://www.j-walk.com/ss/excel/usertips/tip045.htm . It works for me.


"Ruthki" <[email protected]> wrote in
message news:[email protected]...
>
> I would like to use conditional formatting to test whether a cell
> contains text or a formulae and higlight all cells as they are
> overtyped.
>
> I have tried using Type() but doesnt recognise difference between sam a
> number keyed in and a formulae beginning =ifhttps://www.excelforum.com/images/misc/progress.gif" alt="" /> Register To Reply