我以為我已經弄清楚了這一點，但我錯了。我希望 D 列在 C 列中沒有匹配項時突出顯示，但我需要它們在 A 列中屬于同一類別。
A 列是一個下拉串列，可以從以下選項中進行選擇：
(EL), (Kallvatten, Varmvatten, V?rme, IMD), (Fj?rrv?rme, Fj?rrkyla, Hetgas), (IMD Exempel)
我在組周圍加了括號。因此，D 列需要在 C 列中匹配，并且它們需要在同一組（A 列）中。
這是我現在的代碼，它在組之外作業正常。
Dim rng1 As Range, rng2 As Range, x As Long, j As Long, bFault1 As Boolean
bFault1 = False
For x = 8 To Sheets("M?tplan").Range("D" & Rows.Count).End(xlUp).Row
Set rng1 = Sheets("M?tplan").Range("D" & x)
For j = 8 To Sheets("M?tplan").Range("C" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("M?tplan").Range("C" & j)
If (StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0) Or IsEmpty(rng1) Then
rng1.Interior.ColorIndex = xlNone
Set rng2 = Nothing
Exit For
ElseIf (j = Sheets("M?tplan").Range("C" & Rows.Count).End(xlUp).Row) Then
rng1.Interior.Color = RGB(255, 204, 204)
bFault1 = True
End If
Set rng2 = Nothing
Next j
Set rng1 = Nothing
Next x
提前致謝！
uj5u.com熱心網友回復：
在第二個回圈內添加對組的檢查。
Sub macro1()
Dim rng1 As Range, rng2 As Range, x As Long, j As Long
Dim bMatch As Boolean, ws As Worksheet
Dim Grp As String
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
With dict
.Add "EL", 1
.Add "Kallvatten", 2
.Add "Varmvatten", 2
.Add "V?rme", 2
.Add "IMD", 2
.Add "Fj?rrv?rme", 3
.Add "Fj?rrkyla", 3
.Add "Hetgas", 3
.Add "IMD", 4
.Add "Exempel", 4
End With
Set ws = Sheets("M?tplan")
For x = 8 To ws.Range("D" & Rows.Count).End(xlUp).Row
bMatch = False
Grp = dict(Trim(ws.Range("A" & x)))
Set rng1 = ws.Range("D" & x)
For j = 8 To ws.Range("C" & Rows.Count).End(xlUp).Row
' match groups
If Grp = dict(Trim(ws.Range("A" & j))) Then
Set rng2 = ws.Range("C" & j)
If (StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0) Or IsEmpty(rng1) Then
bMatch = True
Exit For
End If
End If
Next j
If bMatch Then
rng1.Interior.ColorIndex = xlNone
Else
rng1.Interior.Color = RGB(255, 204, 204)
End If
Next x
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/401380.html
上一篇：VBA從特定位置的陣列中洗掉專案