|
▼白 さん:
未検証ですが...
Sub Try1()
Dim valA As Variant
Dim rngU As Range
Dim rngB As Range
Dim rngAK As Range
Dim i As Long
Dim m
With Sheets("B")
Set rngB = .Range("B3", .Cells(.Rows.Count, 2).End(xlUp))
Set rngAK = .Range("AK3", .Cells(.Rows.Count, "AK").End(xlUp))
End With
With Sheets("A").Range("A6", Cells(Rows.Count, 1).End(xlUp))
valA = .Value 'A列の値
Set rngU = .Offset(, 20) 'U列
End With
rngU.Interior.ColorIndex = xlNone '始めにU列塗りつぶしなし
For i = 1 To UBound(valA)
If Not IsEmpty(rngU.Item(i).Value) Then
m = Application.Match(rngU.Item(i), rngB, 0)
If IsError(m) Then 'B列に検索値がなかったとき
m = Application.Match(valA(i, 1), rngAK, 0)
If IsNumeric(m) Then
rngU.Item(i).Interior.Color = vbBlue
Else
rngU.Item(i).Interior.Color = vbRed
End If
End If
Else '[U6空白(値が無い)の場合]
m = Application.Match(valA(i, 1), rngAK, 0)
If IsNumeric(m) Then
rngU.Item(i).Interior.Color = vbBlue
End If
End If
Next
MsgBox "処理が終わりました"
End Sub
|
|