Excel 2013 VBA to Highlight Unique Value If Value Found On Any Other Worksheet

[SweetA1217]

Excel 2013 VBA to Highlight Unique Value If Value Found On Any Other Worksheet
Can anyone help me write VBA code to Highlight a Unique Value on a Worksheet (Shop Agenda) if that same unique value is located on another other worksheet in the workbook. Here is the kicker: I cannot hardcode the name of the other worksheets because I have written code to pull multiple workbooks into a single workbook. Therefore, the names of the other worksheets are subject to change.

In Worksheet "Shop Agenda", I want to check if any values in row B (starting at B3 and Range(Selection, Selection.End(xlDown))) is in any other worksheet. If the value is in another worksheet, then highlight that unique value on the "Shop Agenda" worksheet.

I have found code to look at another worksheet for which the worksheet name is known and hardcoded. In this case, the worksheet name will not be known until the worksheet is pulled into the workbook.

Here is the code that I am playing with:

Sub HighlightPriority()
Dim ws As Worksheet
Dim ShopTable As Range
Dim PriorityTable As Range
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim lastRow As Long
Dim cell As Range
Dim Imp_Row As Integer
Dim Imp_Col As Integer

Worksheets("Shop Agenda").Activate
Set ShopTable = Range("B3", Range("B3").End(xlDown))

For Each ws In ThisWorkbook.Worksheets
sSheetName = ActiveSheet.Name
Set PriorityTable = ActiveSheet.Columns("A:Q") '<=== "A:Q" is placeholder, do not know what columns will be on other worksheet

With Worksheets(sSheetName)
lastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

For Each cell In ShopTable
PriorityTable.Cells(Imp_Row, Imp_Col) = Application.WorksheetFunction.VLookup(cell, PriorityTable, 1, False) '<=== Cannot use VLookup b/c will not know where unique value is located
Imp_Row = Imp_Row + 1
If cell.Value = Cells(Imp_Row, Imp_Col) Then
cell.EntireRow.Interior.ColorIndex = 39
Else
cell.EntireRow.Interior.ColorIndex = xlNone
End If
Next cell

End With

Next ws

 

[Fluff]

Hi & welcome to MrExcel.
Could the values from col B occur in any column of the other sheets, or only a specific column?

 

[SweetA1217]

Hi & welcome to MrExcel.
Could the values from col B occur in any column of the other sheets, or only a specific column?

The value could occur in any other column in the other sheets.

Here is another code that I am playing with:
Sub HighlightPriorityAlt()
Dim ShopTable As Range
Dim cell As Range
Dim X As Integer

Worksheets("Shop Agenda").Activate
Set ShopTable = Range("B3", Range("B3").End(xlDown))
Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each cell In ShopTable
For X = 1 To Sheets.Count
On Error Resume Next
Sheets(X).Select

Cells.Find(What:=cell, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
If InStr(1, ActiveCell.Value, cell, vbTextCompare) Then
cell.EntireRow.Interior.ColorIndex = 65535
End If
Next X
Next cell
Application.ScreenUpdating = False
Application.DisplayAlerts = False

End Sub

 

[Fluff]

Ok, how about

Sub SweetA()
   Dim Ary As Variant
   Dim r As Long, c As Long
   Dim Cl As Range
   Dim Ws As Worksheet
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = 1
   With Sheets("pcode")
      For Each Cl In .Range("B3", .Range("B" & Rows.Count).End(xlUp))
         Set Dic(Cl.Value) = Cl
      Next Cl
   End With
   For Each Ws In Worksheets
      If Not Ws.Name = "Pcode" Then
         Ary = Ws.UsedRange.Value2
         If Not IsEmpty(Ary) Then
            For r = 1 To UBound(Ary)
               For c = 1 To UBound(Ary, 2)
                  If Dic.Exists(Ary(r, c)) Then
                     Dic(Ary(r, c)).Interior.ColorIndex = 39
                     Dic.Remove Ary(r, c)
                  End If
               Next c
            Next r
         End If
      End If
   Next Ws
End Sub

 

[SweetA1217]

Thank you for the assistance thus far.

The code is partially working. Unfortunately, it is only finding some of the information, not all of the information. Trying to bound the issue, I have found that if I reduce the number of entries being loaded in the Dic Object, then it finds those entries that were originally missed. The only thing that I can find when bounding the problem is that if I reduce the number of characters in the string of each Dic entry, then it will find those entries. I am perplexed right now.

 

[Fluff]

The number of values in the dictionary should not make any difference.
How long are some of your strings?

 

[SweetA1217]

The number of values in the dictionary should not make any difference.
How long are some of your strings?

The set of data that is not working fully has 100 entries with each entry having a 9 digit number.
I used the same code on a different set of data with 265 entries with each having a 5 digit number, looking at the same auxiliary worksheets. The code worked perfect on this data set.

The only other thing that I can find is that it appears that the code is jumping to the next sheet prematurely.

 

[Fluff]

Are you sure that all your numbers are real numbers & not text?
100 9 digit numbers is not a problem.

 

[SweetA1217]

Are you sure that all your numbers are real numbers & not text?
100 9 digit numbers is not a problem.

I verified that all numbers are real numbers and not text.

 

[Fluff]

Are they all hard value, or the result of a formula?