excel - 验证单元格并获得成绩

Question

这是我的要求。我有一张表，其中 B 列有标记。根据标记并单击按钮，我正在尝试获得成绩并根据成绩为单元格着色。以下是我编写的代码，但这没有成功。请帮忙

Sub SetGrade()
    Dim r As Integer
    Dim Score As Double

    For r = 2 To 20
        Score = Cells(r, 2).Value

        If Score >= 90 And Score <= 100 Then
            ActiveCell(1, 2).Value = "A"
            ActiveCell(1, 2).Interior.ColorIndex = 4
            ActiveCell(1, 2).HorizontalAlignment = xlCenter
        ElseIf Score >= 75 And Score <= 90 Then
            ActiveCell(1, 2).Value = "B"
            ActiveCell(1, 2).Interior.ColorIndex = 46
            ActiveCell(1, 2).HorizontalAlignment = xlLeft
        End If
    Next r
End Sub

Answer 1

我认为你真正需要做的是像这样摆脱 activecell:

Sub SetGrade()
Dim r As Integer
Dim Score As Double

For r = 2 To 20

Score = Cells(r, 2).Value

If Score >= 90 And Score <= 100 Then

    Cells(r, 3).Value = "A"
    Cells(r, 3).Interior.ColorIndex = 4
    Cells(r, 3).HorizontalAlignment = xlCenter

ElseIf Score >= 75 And Score <= 90 Then

    Cells(r, 3).Value = "B"
    Cells(r, 3).Interior.ColorIndex = 46
    Cells(r, 3).HorizontalAlignment = xlLeft

End If

Next r

End Sub