【解決方法】Excel VBAは2つのシートを比較し、2つの基準で重複する値を強調表示します

プログラミングQA

2023.01.31

[ad_1]

0


I am new for doing Excel VBA coding. And I would like to write a simple coding which is highlight the duplicate number

If my ShpMarkLog(test) worksheet SMOrder_No = TALLY-SHEET Order No. Then If my ShpMarkLog(test) worksheet SMNewCTNno = TALLY-SHEET (Column H8 TO AK103) Then Hightlight the duplicate number. Then If the TALLY-SHEET (Column H8 TO AK103) is blank, then put white colour with blank.

But when the system didn't follow my criteria, which is only highlight the number if the SMOrder_No = Order No. Below is my coding.

Option Explicit
Sub Compare()
  Dim sh, wsh As Worksheet
        Set sh = Sheets("ShpMarkLog(test)")
        Set wsh = Sheets("TALLY-SHEET")
        
    Dim x As Long
        x = sh.Range("A" & Rows.Count).End(xlUp).Row
        
    Dim myRange, myRange2 As Range
        Set myRange = sh.Range("I9", "I" & x)
        Set myRange2 = sh.Range("J9", "J" & x)
    Dim p, i As Long
        p = wsh.Range("A" & Rows.Count).End(xlUp).Row
    
    Dim b, b2 As Integer
         
         b = 1
         For i = 8 To 103
            For b2 = 8 To 37
        
        If wsh.Cells(i, b2) = "" Then
            wsh.Cells(i, b2).Interior.ColorIndex = 2
        Else
           If Application.WorksheetFunction.CountIf(myRange2, wsh.Cells(i, b2)) > 0 And Application.WorksheetFunction.CountIf(myRange, wsh.Cells(i, b)) > 0 Then
            wsh.Cells(i, b2).Interior.ColorIndex = 6
           Else
             wsh.Cells(i, b2).Interior.ColorIndex = 2
           End If
           
        End If
             
          Next b2
        Next i
    
End Sub

サンプル画像リンク
[^]
ImgBB でホストされている問題-2 — ImgBB[^]

私が試したこと：

I Want to make the number only highlight duplicate when the criteria is meet. May I know how should I modify the code in order to get my result. Really really thank you