Click here to Skip to main content
15,668,517 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:

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
           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
             wsh.Cells(i, b2).Interior.ColorIndex = 2
           End If
        End If
          Next b2
        Next i
End Sub

Sample Picture Link
problem-2 hosted at ImgBB — ImgBB[^]

What I have tried:

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

Sample Picture.

Sample Excel Link.
Updated 30-Jan-23 22:01pm
Graeme_Grant 31-Jan-23 6:23am    

Excel has this built-in: highlight range > Home > Conditional Formatting > Highlight Cell Rules > Duplicate values. You can then choose your formatting.

If you want to write a macro to do it, start macro recording, then do the steps above on a sample range, then stop recording. Now you have a sample of how it is done for writing your macro...


I've downloaded your sheet and have a solution using a single conditional formatting formula and a new "key" column in your raw data, no macro is required:

1. New key column (note: you may need to apply fixed formatting like below):

This is copied and pasted into every row so the key looks like this:

2. Conditional formatting formula:
=ISNA(MATCH(RIGHT("00000000"&$A8, 8) & "-" & RIGHT("0000" & H8, 4), 'ShpMarkLog(test)'!$P$9:$P$340, 0)) = FALSE

What this does, is takes the 'Tally-Sheet' Order No. (Col A) and the Ctn cells (cols H to AK) and corrects the formatting to look the same as the key column (P) in the 'ShpMarkLog(test)' sheet. it then does a match check on the range of keys and identifies matches. Then, for every match, the Conditional formatting will color a cell.

* excel001.png - Google Drive[^]
* excel002.png - Google Drive[^]
* excel003.png - Google Drive[^]
Share this answer
Raymond Ang 2023 1-Feb-23 19:06pm    
Hi Graeme_Grant,
Thank you for your reply!
I have try your solution and it work for me for same workbook with the sheet ShpMarkLog(test) &
TALLY-SHEET ! Really Thank you!

But May I how if "ShpMarkLog(test)" if form another workbook.
Mean TALLY-SHEET will refer to another workbook excel call ShpMarkLog(test).

I have follow your Conditional formatting and try to modify it but the system show me
"You may not link to other workbook via conditional formatting"
Graeme_Grant 1-Feb-23 20:35pm    
If you're still doing macros, then, from a macro, you could add a sheet to the Tally workbook and generate just the keys. Then in the conditional formatting match function, reference just that column (eg: A:A) in the added sheet, then the conditional formatting will work. The key is what makes conditional formatting wor, just keep it local to the workbook.
Raymond Ang 2023 7-Feb-23 3:30am    
Hi Graeme_Grant,
It work for me! Thank again!
Graeme_Grant 7-Feb-23 3:43am    
If you are happy, please mark as answered so others will know.
Raymond Ang 2023 7-Feb-23 3:46am    
Whilst recording a macro to determine how to do something is often a good idea, Solution 1 will not work in your case as you are not actually looking for "duplicates" - you appear to want to highlight items on your TALLY-SHEET that also appear on sheet ShpMarkLog(test}.

Can I suggest adding a column to TALLY-SHEET, hidden if need be, that contains the formula
That column will then contain either a positive number if the reference is found on the other sheet (it is actually the row number it is found on) OR a zero if not found.

You can then use the technique described in Solution 1 to add Conditional Formatting to the column based on the value in the column we just introduced OR you can loop through that range and colour in other cells based on the value in our new column.
Share this answer
Graeme_Grant 31-Jan-23 5:37am    
Actually, it does work spanning multiple selections across multiple sheets - use the CTRL key for multiple selections. You should check your facts before dismissing them.
CHill60 1-Feb-23 7:57am    
If you re-read what I actually said "Solution 1 will not work in your case as you are not actually looking for duplicates" you will realise that I did not dismiss anything you had written - i.e. the OP had miss-stated their problem. I did actually attempt what you had said - without the benefit of the example that the OP added later. Pot calling the kettle black there
Graeme_Grant 1-Feb-23 9:02am    
As you saw, I changed tact and worked on the solution once the data was there. Still uses conditional formatting 😉
Graeme_Grant 31-Jan-23 7:09am    
My solution, while works for simple matches, was not suitable for a multi-part key match.

I've just re-read your comments and it's similar to what I've done, just a multi-part key was used.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900