vba - 如果然后在范围vba之间

Question

我正在尝试获取此 vba 代码来识别范围之间的值，然后在满足条件时为单元格着色，但是我无法使 if then 语句正确。

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

InputBox返回 String ，并且您永远不会将返回值转换为数字类型。这意味着您正在执行字符串比较，而不是数字比较。如果其中一个字符串比另一个长，它只会根据字符代码比较较短字符串中的字符数:

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

您首先需要验证用户在 InputBox 中输入的内容。实际上是一个数字，然后将其转换为数字类型， 然后 进行比较。我也会摆脱 Goto并以用户不必重新输入有效值的方式构造输入序列:

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

请注意，需要进行额外的测试以避免溢出错误。如果需要浮点数，可以使用 CCur或 CDbl .