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

 

[JoeMo]

Welcome to the board.

Here's a macro you can try.

Sub MrsNight()
Dim myNums As Variant, c As Range, i As Long, j As Long
myNums = Array(5496, 4526, 9600, 1234)
For i = 2 To 50
    With Sheets(i).UsedRange
        For Each c In .Cells
            If Not IsError(c) Then
                For j = 0 To UBound(myNums)
                    If c.Value = myNums(j) Then
                        c.Font.Color = vbRed
                        Exit For
                    End If
                Next j
            End If
        Next c
    End With
Next i
End Sub

 

[Legacy 286899]

Use conditional formatting. Select the cells in which you want to highlight certain cells, and on the Home tab select Conditional Formatting. Select "New Rule and then "Use a formula..." In the "Format values..." box type "=OR($A2=1234, $A2=5678,)", then select the Format button and apply the fill, highlight, or font you want.
If you want to apply this on multiple sheets, you'd have to apply the conditional formatting on each sheet.

 

[Redwolfx]

Change the Red to reflect the Cells that hold your numbers

Sub Find()
Dim WS As Worksheet
Dim Mycell As Range
For Each Mycell In Sheets(1).Range("[COLOR=#ff0000]A1:A5[/COLOR]")
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> Sheets(1).Name Then
            
    Application.ReplaceFormat.Font.Color = RGB(255, 0, 0)
    WS.Cells.Replace What:=Mycell.Value, Replacement:=Mycell.Value, LookAt:=xlWhole, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True
        End If
    Next WS
Next Mycell
End Sub

 

[Rukt]

mrs night, it sounds like conditional formatting will do what you need.

I've assumed the numbers you're looking for appear in cells A1 to A4 on Sheet1, and that the numbers you're testing are in the same range on sheets 2 through 50.

With that in mind, select all the cells on sheet 2 that you want to test. From the Home tab, select Conditional Formatting > New Rule ... > Use a formula. In the formula box, type:

=ISNUMBER(MATCH(A1,Sheet1!$A$1:$A$4,0))

Then click the Format... button, and change the font colour to red. Hit the OK buttons until you get back to the sheet – has 1234 (or any other number) turned red?

If so, great. 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.

Let me know if this doesn't work for you.

Rukt

 

[mrs night]

Welcome to the board.

Here's a macro you can try.

Sub MrsNight()
Dim myNums As Variant, c As Range, i As Long, j As Long
myNums = Array(5496, 4526, 9600, 1234)
For i = 2 To 50
    With Sheets(i).UsedRange
        For Each c In .Cells
            If Not IsError(c) Then
                For j = 0 To UBound(myNums)
                    If c.Value = myNums(j) Then
                        c.Font.Color = vbRed
                        Exit For
                    End If
                Next j
            End If
        Next c
    End With
Next i
End Sub

Hi Joe,

Thank you for the macro. I have tried it and it isn't working, I think that I am not inputting the information correctly. The array has over 100 combinations of 4 digit numbers so it would take me forever to type them individually into the macro, is there an easier way to do this? The worksheet that contains the numbers is in column F rows 2-150 and the data on the other worksheets are in colunm L rows 2-1000. Any further advice?

Thanks again!

Have a great day!

 

[mrs night]

Use conditional formatting. Select the cells in which you want to highlight certain cells, and on the Home tab select Conditional Formatting. Select "New Rule and then "Use a formula..." In the "Format values..." box type "=OR($A2=1234, $A2=5678,)", then select the Format button and apply the fill, highlight, or font you want.
If you want to apply this on multiple sheets, you'd have to apply the conditional formatting on each sheet.

Hi oertell,

Thank you for the formula. I have tried it and it isn't working, I think that I am not inputting the information correctly. I have over 100 combinations of 4 digit numbers so it would take me forever to type them individually into the formula, is there an easier way to do this? The worksheet that contains the numbers is in column F rows 2-150 and the data on the other worksheets are in colunm L rows 2-1000. Any further advice?

Thanks again!

Have a great day!

 

[mrs night]

Change the Red to reflect the Cells that hold your numbers

Sub Find()
Dim WS As Worksheet
Dim Mycell As Range
For Each Mycell In Sheets(1).Range("[COLOR=#ff0000]A1:A5[/COLOR]")
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> Sheets(1).Name Then
            
    Application.ReplaceFormat.Font.Color = RGB(255, 0, 0)
    WS.Cells.Replace What:=Mycell.Value, Replacement:=Mycell.Value, LookAt:=xlWhole, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True
        End If
    Next WS
Next Mycell
End Sub

Hi Redwolfx,

Thank you for the macro. I have tried it and it isn't working, I think that I am not inputting the information correctly. The worksheet that contains the numbers is in column F rows 2-150 and the data on the other worksheets are in colunm L rows 2-1000. Any further advice?

Thanks again!

Have a great day!

 

[mrs night]

mrs night, it sounds like conditional formatting will do what you need.

I've assumed the numbers you're looking for appear in cells A1 to A4 on Sheet1, and that the numbers you're testing are in the same range on sheets 2 through 50.

With that in mind, select all the cells on sheet 2 that you want to test. From the Home tab, select Conditional Formatting > New Rule ... > Use a formula. In the formula box, type:

=ISNUMBER(MATCH(A1,Sheet1!$A$1:$A$4,0))

Then click the Format... button, and change the font colour to red. Hit the OK buttons until you get back to the sheet – has 1234 (or any other number) turned red?

If so, great. 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.

Let me know if this doesn't work for you.

Rukt

Hi Rukt,

Thank you for the formula. I have tried it and it isn't working, I think that I am not inputting the information correctly. The worksheet that contains the numbers is in column F rows 2-150 and the data on the other worksheets are in colunm L rows 2-1000. Any further advice?

Thanks again!

Have a great day!

 

[Legacy 286899]

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?