Find and Replace Macro

pyair

New Member
Joined
Nov 12, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
What does the code look like for the single instance that you mentioned? Did it work for the single instance?
 
Upvote 0
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?
 
Upvote 0
See if the following works:

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top