Prevent Hard Inputs

F

Frederik12

How can I programs cells such that in case a hard input is used in a
formula the cell turns red (using conditional formatting).

Hard input in a formula is:
"+" and any digit
"-" and any digit
"*" and any digit
"/" and any digit

Regards,

Frederik
 
B

Bob Umlas

You can define a function to return true if it contains one of those
characters, then use conditional formatting in that range to turn cells red
and reference the function, like
=SpecChar(A1) where A1 is the active cell when you use the conditional
formatting, and SpecChar is defined as:
Public Function SpecChar(rg) As Boolean
For Each thing In rg
If InStr(thing.Formula, "+") > 0 Or _
InStr(thing.Formula, "-") > 0 Or _
InStr(thing.Formula, "/") > 0 Or _
InStr(thing.Formula, "*") > 0 Then
SpecChar = True
Exit Function
End If
Next
End Function
 
V

vezerid

Alternatively, you can use the following formula in CF (with the
FormulaIs option)

=AND(OR(LEFT(A1,1)={"+","-","*","/"}),LEN(A1)=2,ISNUMBER(VALUE(MID(A1,2,1))))

HTH
Kostis Vezerides
 
F

Frederik12

Bob said:
You can define a function to return true if it contains one of those
characters, then use conditional formatting in that range to turn cells
red
and reference the function, like
=SpecChar(A1) where A1 is the active cell when you use the conditional
formatting, and SpecChar is defined as:
Public Function SpecChar(rg) As Boolean
For Each thing In rg
If InStr(thing.Formula, "+") > 0 Or _
InStr(thing.Formula, "-") > 0 Or _
InStr(thing.Formula, "/") > 0 Or _
InStr(thing.Formula, "*") > 0 Then
SpecChar = True
Exit Function
End If
Next
End Function
Click to expand...

Bob,

thanks, but how do I define a function?

KR
 
Top