If then Between range vba

Question

I am trying to get this vba code to identify values between a range and then colour the cell if the condition is met, however I cannot get the if then statement correct.

Option Explicit

Sub TestRange()
    Dim Str, lst, y, Value1, Value2
    Dim Rng As Range

    Sheets("Test").Activate
    Str = Sheets("Test").Range("A2").Address
    lst = Sheets("Test").Range("A2").Cells.SpecialCells(xlCellTypeLastCell).Address

    Sheets("Test").Range(Str & ":" & lst).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -4.99893185216834E-02
            .PatternTintAndShade = 0
        End With

    here:

    Value1 = InputBox("Please enter the lowest score in your range", "CS2")
    Value2 = InputBox("Please enter the highest score in your range", "CS2")


    If Value2 < Value1 Then
    MsgBox "Your Second Value is smaller than your first value" & vbNewLine & _
            "Please submit a value higher than your first value", vbExclamation
    GoTo here
    End If


    Set Rng = Sheets("Test").Range(Str & ":" & lst)
    For Each y In Rng
    If y >= Value1 And y <= Value2 Then
        y.Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
    End If
    Next y
End Sub

Answer 1

There is a Range.Value2 property. Try not to repurpose reserved words, particularly when there is ambiguous methods.

The Excel application InputBox method allows you to specifically request a number. Why not simply add some overhead for people that do not like to follow instructions?

The method of determining the last cell in the range of str and lst was flawed but I believe that I've rectified it.

Sub TestRange()
    Dim val1 As Double, val2 As Double, tmp As Double
    Dim y As Range, rng As Range, str As Range, lst As Range

    With Worksheets("Test")
        Set str = .Range("A2")
        Set lst = .Range("A" & .Rows.Count).End(xlUp)
        Set rng = .Range(str, lst)

        With rng.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -4.99893185216834E-02
            .PatternTintAndShade = 0
        End With

        val1 = Application.InputBox("Please enter the lowest score in your range", "CS2", Type:=1)
        val2 = Application.InputBox("Please enter the highest score in your range", "CS2", Type:=1)

        If val2 < val1 Then
            tmp = val2
            val2 = val1
            val1 = tmp
        End If


        For Each y In rng
            If y.Value2 >= val1 And y.Value2 <= val2 Then
                With y.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent2
                    .TintAndShade = 0.799981688894314
                    .PatternTintAndShade = 0
                End With
            End If
        Next y
    End With
End Sub

tbh, I do not know why Conditional Formatting with a native worksheet formula is not a better solution. The user input could be adjusted for.

Answer 2

InputBox returns a String, and you never cast the return value to a numeric type. That means you are performing string comparisons, not numeric comparisons. If one of the strings is longer than the other, it only compares the number of characters in the shorter string based on their character codes:

Private Sub Example()
    Debug.Print "10" > "5"  'This returns false.
End Sub

You first need to validate that what the user typed in the InputBox is actually a number, then cast it to a numeric type, then perform your comparisons. I'd also get rid of the Goto and structure the input sequence in a way that the user doesn't have to re-enter valid values:

Dim userInput As String
Dim firstValue As Long
Dim secondValue As Long
Dim validInput As Boolean

Do
    userInput = InputBox("Please enter the lowest score in your range", "CS2")
    If IsNumeric(userInput) Then
        firstValue = CLng(userInput)
        validInput = True
    Else
        MsgBox "Lowest score must be a number."
    End If
Loop While Not validInput

Do
    validInput = False
    userInput = InputBox("Please enter the highest score in your range", "CS2")
    If IsNumeric(userInput) Then
        secondValue = CLng(userInput)
        If secondValue > firstValue Then
            validInput = True
        Else
            MsgBox "Your Second Value is smaller than your first value" & vbNewLine & _
                   "Please submit a value higher than your first value", vbExclamation
        End If
    Else
        MsgBox "Highest score must be a number."
    End If
Loop While Not validInput

Note that there is additional testing needed to avoid overflow errors. If you need a floating point number, you can use CCur or CDbl.