Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]() 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? -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=380978 |
#2
![]() |
|||
|
|||
![]() 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 |
#3
![]() |
|||
|
|||
![]()
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 |
#4
![]() |
|||
|
|||
![]() Thanks Anil - It worked and I learnt a bit more. -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=380978 |
#5
![]() |
|||
|
|||
![]() 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 |
#6
![]() |
|||
|
|||
![]()
Try http://www.j-walk.com/ss/excel/usertips/tip045.htm . It works for me.
"Ruthki" wrote in message ... 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? -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=380978 |
#7
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Test a cell for a formula present | Excel Discussion (Misc queries) | |||
Function syntax to compare cell contents | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |