VBA Find and Replace but color entire column

Question

I'm fairly new to VBA (as in this is my first attempt with it) and am working on a macro to find and replace a large set of numbers with slightly modified numbers in a spreadsheet named "AA SERIES". I'm storing these in a spreadsheet named "PartNumbers", with the existing ones in column I and the replacements in J. The code shown below works fine for this:

Sub Macro1()  
Dim i As Integer  
Dim WS As Worksheet  
Dim FindStr As String  
Dim RepStr As String  
For i = 1 To 87

   For Each WS In Workbooks("AA SERIES").Worksheets  
    FindStr = Workbooks("PartNumbers").Sheets("Sheet1").Range("I" & i).Value  
    RepStr = Workbooks("PartNumbers").Sheets("Sheet1").Range("J" & i).Value  
    Cells.Replace What:=FindStr, Replacement:=RepStr

   Next  
Next i

End Sub

However, what I'd like it to do is also format the entire column a different color (ideally light purple) if the macro replaces a value in it. The goal is that the next person to work with this sheet will be able to quickly scroll through and see where the changes are.

Any suggestions?

Are you looking to color the whole column or the row? It seems like if you are looping through rows, the column would not do much good. — Cohan, Jun 30 '15 at 19:50
The best way to learn VBA is to record a macro that kind of does what you want, and then adapt it to your actual needs. Learning this way WILL make you a VBA genius. — 3-14159265358979323846264, Jun 30 '15 at 19:51
Pi speaks the truth. That is how I've suggested many people learn at first. Once you get the hang of it, you'll see/do stuff that you don't like how the record feature does it and continue to improve. — Cohan, Jun 30 '15 at 19:53
I concur, using Pi's method will teach you what settings you need and how to access them. It will also teach you horrible coding practices, so some time spent learning better coding practices will be well worth it. A good place to start your reading is [how to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — FreeMan, Jun 30 '15 at 20:12

Pira · Answer 1 · 2015-06-30T22:24:17.997

I think this is what you are looking for:
https://msdn.microsoft.com/en-us/library/bb209118%28v=office.12%29.aspx

Cant verify it at the moment... but I think it works like this:

Application.ReplaceFormat.Interior.Color = RGB(200, 150, 200)

Cells.Replace What:=FindStr, Replacement:=RepStr, SearchFormat:=False, ReplaceFormat:=True

EDIT
To color the entire column, you have to search for the lightpurple colored cells and apply the color change for the entirecolumn of each found cell

Application.FindFormat.Interior.Color = RGB(200, 150, 200)
Cells.Find(SearchFormat:=True).EntireColumn.Interior.Color = RGB(200, 151, 200)
'slightly changed colorcode to avoid endless loop, if you want to loop through all changed cells

score 0 · Accepted Answer · answered Jul 01 '15 at 05:12

To expand on the previous answer:

Option Explicit

Sub replace1()

    Const ENTIRE_COLUMN As Byte = 0     'Change to 1 to color the entire columns

    Dim i       As Integer
    Dim ws      As Worksheet
    Dim findStr As String
    Dim repStr  As String
    Dim lPurple As Long
    Dim found   As Range
    Dim first   As String

    lPurple = RGB(244, 233, 255)

    Application.ReplaceFormat.Interior.Color = lPurple

    For Each ws In Workbooks("AA SERIES").Worksheets
        For i = 1 To 9
            With Workbooks("PartNumbers").Sheets("Sheet1")
                findStr = .Range("I" & i).Value
                repStr = .Range("J" & i).Value

                ws.UsedRange.Replace What:=findStr, _
                                     Replacement:=repStr, _
                                     ReplaceFormat:=True

                If ENTIRE_COLUMN = 1 Then
                    With ws.UsedRange
                        Set found = .Find(What:=repStr, SearchOrder:=xlByRows)
                        If Not found Is Nothing Then
                            first = found.Address
                            Do
                                If found.Offset(1).Interior.Color <> lPurple Then
                                    .Columns(found.Column).Interior.Color = lPurple
                                End If
                                Set found = .FindNext(found)
                            Loop While Not found Is Nothing And found.Address <> first
                        End If
                    End With
                End If

            End With
        Next
    Next
End Sub

VBA Find and Replace but color entire column

2 Answers2