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

4thorns · Apr 23, 2015

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 · Apr 23, 2015

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 · Apr 24, 2015

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 · Apr 25, 2015

Hello,

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 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 · May 7, 2015

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 · May 15, 2015

@TerryScan, wrong thread!

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

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

4thorns

Structural

Recommended for you

IDS

Civil/Environmental

MortenA

Chemical

onlyadrafter

Mechanical

TerryScan

Civil/Environmental

SkipVought

Aerospace

Similar threads

Part and Inventory Search

Sponsor