Conditional format based on values, characters and spaces (feet and fractional inches)

[4thorns]

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

 

[IDS]

I don't use conditional formatting a lot, but it looks like if you write a UDF to return TRUE or FALSE depending on whether the text fits the pattern or not, then you can use that as a conditional format formula.

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-conditional-formatting/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MortenA]

I thnk you can format a cell so that input has to _be_ in a specific format - not jest test it. Try to look in hlp and search for "validate". Im not familiar with the function myself

 

[onlyadrafter]

Hello,

Have tried this code on a few different entries, and it seems to work.

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 Sub

Change 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

 

[TerryScan]

For the "JOB" cell, I had luck with using a custom format for the cell and using the following in the custom Type:
"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).

 

[SkipVought]

@TerryScan, wrong thread!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}