Excel problem: I need to let only values between a certain range be in a column and if they are less

Ps18priyanka 

You could use Data Validation to allow only values between a specified minimum and maximum:

If you want to allow the user to enter values outside the range but to convert them automatically to the minimum/maximum, that would require VBA:

• Right-click the sheet tab.
• Select View Code from the context menu.
• Copy the code listed below into the worksheet's code module.
• Switch back to Excel.
• Save the workbook as a macro-enabled workbook (*.xlsm).
• Make sure that you allow macros when you open the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Change these as required
    Const TheMin = 20
    Const TheMax = 40
    Dim rng As Range
    Dim cel As Range
    ' Change the range below to the range you want to restrict
    Set rng = Range("D2:D1000")
    If Not Intersect(rng, Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each cel In Intersect(rng, Target)
            If cel.Value <> "" Then
                If cel.Value < TheMin Then
                    cel.Value = TheMin
                ElseIf cel.Value > TheMax Then
                    cel.Value = TheMax
                End If
            End If
        Next cel
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Ps18priyanka 

It's hard to give specific advice without a more detailed description. Where, for example, are the values coming from?

If you are using formulas to populate the cells in the column you're referring to, you can nest the formula in a couple of IF statements such as this. If the sum of A2 + B2 is greater than 3 and less than 20, it appears as calculated; if it exceeds either the lower or upper limit, that limit appears.

=IF((A2+B2)<3,3,IF((A2+B2)>20,20,A2+B2))

You could also use Data Validation to preclude entering values that exceed a specified minimum or maximum. However, other than an error message, that doesn't display the min or max allowed; just keeps somebody from typing in an invalid number.

So it would be helpful for us in helping you if you were to give a bit more of the bigger picture which this requirement is a part of.