Conditional format based on values, characters and spaces (feet and fractional inches)
Conditional format based on values, characters and spaces (feet and fractional inches)
(OP)
I'm trying to foolproof a spreadsheet. I would like the user to input feet and inches in a certain format. I need a formula to use in conditional formatting that highlights a cell if the input is not correct.
It must be input as in these examples, 12'-6" or 12'-6 1/2"
The correct procedure is;
1: any number
2: the foot sign
3; a dash
4: any number
5: a space (if using a fraction)
6: the fraction (if using a fraction)
7: the inch sign
Is this even possible?
Thanks, Doug
It must be input as in these examples, 12'-6" or 12'-6 1/2"
The correct procedure is;
1: any number
2: the foot sign
3; a dash
4: any number
5: a space (if using a fraction)
6: the fraction (if using a fraction)
7: the inch sign
Is this even possible?
Thanks, Doug
Talk To Other Members
RE: Conditional format based on values, characters and spaces (feet and fractional inches)
But have a look at this bog post regarding the need for error checking in the UDF:
http://excelmatters.com/2014/06/17/udfs-and-condit...
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Conditional format based on values, characters and spaces (feet and fractional inches)
RE: Conditional format based on values, characters and spaces (feet and fractional inches)
Have tried this code on a few different entries, and it seems to work.
CODE
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then If Format(Target.Value, "0" & "'-" & "0") <> Target.Value Then MsgBox "WRONG" Target.ClearContents End End If If Format(Target.Value, "0" & "'-" & "0 0" & "/" & "0") <> Target.Value Then MsgBox "WRONG" Target.ClearContents End If End If End SubChange cell ref as required. The code needs to go into the relevant sheet code windon, not a standard module.
----------------------------------
Hope this helps.
----------------------------------
been away for quite a while
but am now back
RE: Conditional format based on values, characters and spaces (feet and fractional inches)
"JOB:" @
For the check boxes: I was able to successfully create a row that works as desired, but if I copy those boxes to other rows, they act strange like in your original sheet (when expanded, movement is not predictable and duplicate boxes are created).
RE: Conditional format based on values, characters and spaces (feet and fractional inches)
Skip,
Just traded in my OLD subtlety...
for a NUance!