Need macro or formula to find & highlight specific numbers in multiple worksheets

[mrs night]

Hi Everyone,

I am looking to select a column with numerous 4 digit numbers in it and have any of those numbers in the column be automatically highlighted or font color changed in multiple worksheets. Example:

worksheet 1

4 Digits need to found in all other worksheets

5496
4526
9600
1234

worksheets 2-50

6549
7596
1234
9865

Can anyone help me? I have never done a macro before and can't find a formula that will do it for me.

Thank you for your time!

mrs night

 

[Redwolfx]

Based off your descriuption Try

Sub Find()
Dim WS As Worksheet
Dim Mycell As Range
For Each Mycell In Sheets(1).Range("F2:F150")
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> Sheets(1).Name Then
            
    Application.ReplaceFormat.Font.Color = RGB(255, 0, 0)
    WS.Columns("J:J").Replace What:=Mycell.Value, Replacement:=Mycell.Value, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Next WS
Next Mycell
End Sub

You will need to add this to a module.

 

[JoeMo]

Based off your descriuption Try

Sub Find()
Dim WS As Worksheet
Dim Mycell As Range
For Each Mycell In Sheets(1).Range("F2:F150")
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> Sheets(1).Name Then
            
    Application.ReplaceFormat.Font.Color = RGB(255, 0, 0)
    WS.Columns("J:J").Replace What:=Mycell.Value, Replacement:=Mycell.Value, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Next WS
Next Mycell
End Sub

You will need to add this to a module.

That should work after adding an End If, changing the search column to "L", and setting the searchformat and replaceformat arguments to True.

Sub Find()
Dim WS As Worksheet
Dim Mycell As Range
For Each Mycell In Sheets(1).Range("F2:F150")
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> Sheets(1).Name Then
            Application.ReplaceFormat.Font.Color = RGB(255, 0, 0)
            WS.Columns("L:L").Replace What:=Mycell.Value, Replacement:=Mycell.Value, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
            ReplaceFormat:=True
        End If
    Next WS
Next Mycell
End Sub

 

[Redwolfx]

Thank you Joe, I'm not sure exactly what happened to my code. I think I copied and pasted a different test. I appreciate the assist

 

[Rukt]

The worksheet that contains the numbers is in column F rows 2-150 and the data on the other worksheets are in column L rows 2-1000.

Okay, then it should just be a matter of:

• select cells L2 to L1000
• Home tab > Conditional Formatting > New Rule... > Use a formula
• Copy & paste this formula (red bits are updated to reflect your cells): =ISNUMBER(MATCH(L2,Sheet1!$F$2:$F$150,0))
• Apply and check whether it works now
• Follow the rest of the instructions as before:

We're going to apply that formatting to the rest of the sheets. On the Home tab, click 'Format Painter' (your cursor should get a brush next to it). Click on sheet 3. Now, while holding shift, click on sheet 50. You'll see that all the tabs from sheet 3 to sheet 50 turn white. Click and drag over all the cells you want to search. The brush should disappear from your cursor, and all the numbers you're looking for should turn red. Before doing anything else, click on sheet 2 again, so that the other sheets turn grey again.

Good luck!

 

[mrs night]

Let me see if I understand the problem correctly: you have a workbook with, say, Sheet1, Sheet2, Sheet3, and Sheet4. On Sheet1 Column D you have a list of four-digit numbers. If any of those numbers exist in, say, Column F on any other sheet, you want them highlighted in those sheets but not necessarily Sheet1. Is this correct?

Hi oertell,

That is correct. Any suggestions on how I can do this?

Thanks!