Find and Replace Macro

[pyair]

I'm trying to make a find and replace macro where the text remains the same but only the format changes. I have a large list of products for example labeled 1-10,000 and I only need to highlight certain numbers within the list. I tried to make a macro for one single instance, then expand it into a range, but keep getting an error. Can anyone help? Thank you.

Dim arrayID() As Variant


'
With Application.ReplaceFormat.Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Replace What:=arrayID(E95:E15531), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=True, FormulaVersion:=xlReplaceFormula2

 

[Rick Rothstein]

I am hoping that will be the response, otherwise, seems kind of a pointless exercise.

I edited my response to add some more detail which you may have missed (you were too quick for me).

 

[johnnyL]

I agree with the edit.

 

[pyair]

Sorry for the confusion.

List A contains product numbers with duplicates in a single column within the sheet, that can span from cells E2:E15531 like in my previous post. This can be sorted in a variety of different ways, but I only need certain product numbers.

List B is a much smaller sample size in a column and has the certain product numbers with no duplicates. I need to highlight the product numbers found in List B to List A, so I can sort by the highlighted values only in List A. I'm only interested in the product numbers in List B and need a simple way to find them in List A.

 

[Rick Rothstein]

Since you did not tell us, I assumed "List B" was located in Column H starting on Row 2. With that said, give the following macro a try...

Sub HighlightFoundCells()
  Dim ListB As Variant, V As Variant
  ListB = Range("H2", Cells(Rows.Count, "H").End(xlUp)).Value
  With Application
    .ScreenUpdating = False
    .ReplaceFormat.Clear
    .ReplaceFormat.Interior.Color = vbYellow
    For Each V In ListB
      Columns("E").Replace V, "", xlWhole, , , , False, True
    Next
    .ReplaceFormat.Clear
    .ScreenUpdating = True
  End With
End Sub

 

[pyair]

You are correct in that List B product numbers are in column H.

I just tried to run the macro and it only highlighted blank cells with no data inside, like the cell above the item label. The first item number in List B "03447" was not highlighted.

Is this an issue with the range of the source in List B? This is an image of the List B. The rows are not sequential, does that affect the macro in anyway?

 

[johnnyL]

How about this:

Sub HighlightCellIfValueExistsinAnotherColumn()
'
    Dim Cell_1  As Range
    Dim Cell_2  As Range
    Dim ws      As Worksheet
'
    Application.ScreenUpdating = False
'
    Set ws = Worksheets("Sheet1")                                                           ' <--- Set this to the proper sheet name
'
    For Each Cell_2 In Range("H3:H" & Range("H" & Rows.Count).End(xlUp).Row)                ' For every cell in the range ...
        For Each Cell_1 In Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row)            ' For every cell in the range ...
            If Cell_1.Value = Cell_2.Value Then
                ws.Range("E" & Cell_1).Interior.ColorIndex = 6
            End If
        Next
    Next
'
    Application.ScreenUpdating = True
End Sub

 

[johnnyL]

Sorry, code I posted in my last post is wrong.

Try the following:

Sub HighlightCellIfValueExistsinAnotherColumnV2()
'
    Dim Cell_1  As Range
    Dim Cell_2  As Range
    Dim ws      As Worksheet
'
    Application.ScreenUpdating = False
'
    Set ws = Worksheets("Sheet1")                                                               ' <--- Set this to the proper sheet name
'
    For Each Cell_2 In ws.Range("H3:H" & ws.Range("H" & Rows.Count).End(xlUp).Row)              ' For every cell in the range ...
        For Each Cell_1 In ws.Range("E2:E" & ws.Range("E" & Rows.Count).End(xlUp).Row)          ' For every cell in the range ...
            If Cell_1.Value = Cell_2.Value Then ws.Range(Cell_1.Address).Interior.ColorIndex = 6
        Next
    Next
'
    Application.ScreenUpdating = True
End Sub

 

[pyair]

The code didn't work but I found a work around using vlookup so I no longer need to manually enter the data.

Thank you!