Excel Forum

Code: Select all

Private Sub CheckSA2100_Click()
Application.ScreenUpdating = False
Dim r, r1, r2, rCode, rCode1 As Range
Dim rCTCode As String

With Sheets("List_User")
    Set rCode = .Range("c6:c16").SpecialCells(xlCellTypeConstants)
End With

For Each r In rCode
'    If r.Value = "**" Then Exit For
    rCTCode = r.Value
        
        With Sheets("SA2100000")
            Set rCode1 = .Range("c:c").SpecialCells(xlCellTypeConstants)
            For Each r1 In rCode1
                If r1.Value = rCTCode Then
                    r.Offset(, 19) = "Yes"
                    r.Offset(, 19).Font.Color = RGB(0, 0, 0)
                ElseIf r1.Value <> rCTCode Then
                    If r.Offset(, 19) = "" Then
                        r.Offset(, 19) = "No"
                        r.Offset(, 19).Font.Color = RGB(255, 0, 0)
                    End If
                End If
            Next r1
        End With
Next r
Application.ScreenUpdating = True
Sheet1.Select
End Sub

Code: Select all

'...

Dim rCTCode As String, l As Integer

With Sheets("List_User")
    l = Application.Match("SA2100", .Range("B:B"), 0) + 1
'    Set rCode = .Range("c6:c16").SpecialCells(xlCellTypeConstants)
    Set rCode = .Range("C" & l, .Range("C" & l).End(xlDown))
End With
'...