VBA Find and Replace but color entire column

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:

我是VBA的新手(因为这是我第一次尝试使用它),我正在开发一个宏来查找和替换一组数字,这些数字在名为“AA SERIES”的电子表格中略有修改。我将这些存储在一个名为“PartNumbers”的电子表格中,其中现有的列在第一列,而在J中替换。下面显示的代码适用于此:

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?

2 个解决方案

#1

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

#2

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

我想这就是你要找的东西: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

#1