Spreadsheet validation

[Guest]

Is there a way to set column "D" on a sheet to only accept (or automatically
round) a value in 0.50 increments?

Examples:

25.5 < ok
100 < ok
18.50 < ok
0.5 < ok
25.25 < invalid
35.3 < invalid

How can I do this?

Thanks much in advance

 

[Bob Phillips]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If IsNumeric(.Value) Then
.Value = Round(.Value / 0.5, 0) * 0.5
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Fredrik Wahlgren]

Is there a way to set column "D" on a sheet to only accept (or automatically
round) a value in 0.50 increments?

Examples:

25.5 < ok
100 < ok
18.50 < ok
0.5 < ok
25.25 < invalid
35.3 < invalid

How can I do this?

Thanks much in advance

I don't know how to do this on the entire column, but I can show you how to
do this for a sinle cell like D1
1) Select cell D1
2) Open Data|Validation
3) Select Settings and Custom
4) Enter this =IF(OR(0 = D1 - INT(D1), 0.5 = D1-INT(D1)), True, false)

Unfortunately, I have the Swedish version of Excel so my translations may
not be correct. This function will work with negative values as well.

/Fredrik