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

 

[johnnyL]

What does the code look like for the single instance that you mentioned? Did it work for the single instance?

 

[pyair]

Sorry I should have been more clear.

The code I posted was the single instance of a recorded macro, because I am not too familiar with VBA, so cannot write it from scratch. Using the single instance, I tried to make it find and replace a range of values instead of the single cell. If I reuse the macro that I recorded, then it does highlight the one desired value. However I need to highlight a range of values, so I'm not sure how to proceed. Can I use the replace function for a list of values or would I need to make a loop and cycle through the values in a specific column?

 

[johnnyL]

See if the following works:

Sub ColorChange()
'
    Application.FindFormat.Clear
    Application.ReplaceFormat.Clear
'
    With Application.FindFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'
    With Application.ReplaceFormat.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'
'    Range("E95:E15531").Replace What:="", Replacement:="", LookAt:=xlPart, _
'
    Range("E2:E15531").Replace What:="", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=True, FormulaVersion:=xlReplaceFormula2
End Sub

 

[pyair]

It didn't work, but I'm not sure I did the initial macro correctly or not. I have a two lists A and B. A is numbered from 1-10,000 and B is a set of random numbers between 1-10,000. I've been manually copying and replacing the numbers from B to A with the same number but highlighted so I can sort the entire list by only the highlighted values afterwards.

 

[johnnyL]

What exactly are you wanting the code to do?

 

[pyair]

Sheet 1 has List A containing numbers 1-10,000 and it needs to be sorted.

Sheet 2 has List B containing random numbers between 1-10,000.

I need a macro that can search for each value in List B and replace the exact same value except with a highlighted cell in List A. Once this process is complete, I can sort manually by highlighted cells to get the data I need.

 

[johnnyL]

The part that I think I understand: search column B in sheet2 for a match in column A in sheet 1. If found, highlight that cell in sheet 1 Column A yellow.

Is sheet1 column A numbered sequentially from 1 to 10,000?

Is sheet2 column B numbered randomly from 1 to 10,000 with no duplicates?

If all of that is correct, wouldn't every single cell in sheet 1 column A be highlighted yellow at the end of the code running?

What am I missing?

 

[Rick Rothstein]

I think Sheet2 Column B does not have a total of 10,000 cells filled with numbers, rather, I think it has much less cells but what is in those cells are numbers somewhere between 1 and 10,000 (that is, not all 10,000 numbers appear in Sheet2 Column B). With that said, one thing I'm confused on is the OP says in Message #7, "Sheet 1 has List A containing numbers 1-10,000 and it needs to be sorted" but early he said List A is in order... so why does it need to be sorted? I think the OP needs to tell us clearly what cells on Sheet1 has what in it and what cells in Sheet2 contain values. Now, I am saying cells but earlier the OP called them Lists which might mean cells, but it does not have to which is why I think the OP needs to explain everything in more detail.

 

[johnnyL]

I think Sheet2 Column B does not have a total of 10,000 cells filled with numbers, rather, I think it has much less cells but what is in those cells are numbers somewhere between 1 and 10,000 (that is, not all 10,000 numbers appear in Sheet2 Column B).

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