Change barcode lookup from sheet to workbook

Question

I have a workbook that has 4 different sheets for inventory purposes (each sheet corresponds to a type of inventory to make organizing easier). Not the best setup, but I have a barcode lookup system where I scan the item's barcode and Excel finds and highlights the corresponding row (containing info like name, picture, quantity, etc.) that's all updated manually. Originally everything was on one sheet but recently I have sorted them out into 4 different sheets. Since then the barcode lookup only works on the original inventory sheet (makes sense since the code was only for that one sheet). I have been unable to figure out how to change the code to work on the whole workbook. I have tried to change worksheet to workbook (didn't work), then I tried to add a Set ws = ThisWorkbook.Sheets("") for each sheet (also didn't work) and a handful of other changes. If anyone has any idea on how to change it so it searches the workbook instead of the one sheet I would appreciate it. Here is a copy of the working code for the first sheet:

Private Sub CommandButton1_Click()

 Dim ws As Worksheet
 Set ws = ThisWorkbook.Sheets("Inventory List")

 Dim rangeToLook As Range
 Set rangeToLook = ws.Range("C3:C1000")

 Dim wholeRange As Range
 Set wholeRange = rangeToLook.Resize(, 10)

 ' change 14408667 to yours grey color code here
 wholeRange.Cells.Interior.Color = 14408667

 Dim code As Variant
     code = InputBox("Please scan a barcode and hit enter if you need to")

 Dim matchedCell As Range
 Set matchedCell = rangeToLook.Find(what:=code, LookIn:=xlValues, _
                   lookat:=xlWhole, MatchCase:=True)

     If Not matchedCell Is Nothing Then
         With matchedCell
             Application.Goto .Cells(1)
             .Resize(1, 10).Interior.ColorIndex = 20
         End With
     Else
         MsgBox "Barcode Not Found"
     End If

 End Sub

Thank you for the help in advance.

Answer 1

Try this code:

Private Sub CommandButton1_Click()

 Dim ws As Worksheet
 Dim rangeToLook As Range
 Dim wholeRange As Range
 Dim code As Variant
 Dim matchedCell As Range
 
 code = InputBox("Please scan a barcode and hit enter if you need to")
 
 For Each ws In ThisWorkbook.Sheets
    Set rangeToLook = ws.Range("C3:C1000")
    Set wholeRange = rangeToLook.Resize(, 10)

    wholeRange.Interior.Color = 14408667

    Set matchedCell = rangeToLook.Find(what:=code, LookIn:=xlValues, _
                   lookat:=xlWhole, MatchCase:=True)

    If Not matchedCell Is Nothing Then
        With matchedCell
            Application.Goto .Cells(1)
            .Resize(1, 10).Interior.ColorIndex = 20
            Exit For
        End With
    End If
 Next
 
 If matchedCell Is Nothing Then
    MsgBox "Barcode Not Found"
 End If
 
 End Sub