VBA宏可比较两列并以颜色突出显示单元格差异 [英] VBA macro to compare two columns and color highlight cell differences

问题描述

我想给高亮显示彼此不同的单元格上色；在这种情况下，colA和colB.此功能可以满足我的需要，但看起来重复，难看且效率低下.我不太熟悉VBA编码；有没有更优雅的方式编写此函数?

I wanted to color highlight cells that are different from each other; in this case colA and colB. This function works for what I need, but looks repetitive, ugly, and inefficient. I'm not well versed in VBA coding; Is there a more elegant way of writing this function?

编辑我正在尝试让此功能执行的是:1.在ColA中高亮显示ColB中不同或不同的单元格2.在ColB中突出显示与ColA中不同的单元格

EDIT What I'm trying to get this function to do is: 1. highlight cells in ColA that are different or not in ColB 2. highlight cells in ColB that are different or not in ColA

    Sub compare_cols()

    Dim myRng As Range
    Dim lastCell As Long

    'Get the last row
    Dim lastRow As Integer
    lastRow = ActiveSheet.UsedRange.Rows.Count

    'Debug.Print "Last Row is " & lastRow

    Dim c As Range
    Dim d As Range

    Application.ScreenUpdating = False

    For Each c In Worksheets("Sheet1").Range("A2:A" & lastRow).Cells
        For Each d In Worksheets("Sheet1").Range("B2:B" & lastRow).Cells
            c.Interior.Color = vbRed
            If (InStr(1, d, c, 1) > 0) Then
                c.Interior.Color = vbWhite
                Exit For
            End If
        Next
    Next

    For Each c In Worksheets("Sheet1").Range("B2:B" & lastRow).Cells
        For Each d In Worksheets("Sheet1").Range("A2:A" & lastRow).Cells
            c.Interior.Color = vbRed
            If (InStr(1, d, c, 1) > 0) Then
                c.Interior.Color = vbWhite
                Exit For
            End If
        Next
    Next

Application.ScreenUpdating = True

End Sub

推荐答案

是的，是的，我整天都在做.实际上，您的代码看起来很像我的方式.虽然，我选择使用整数循环而不是使用"For Each"方法.我在您的代码中看到的唯一潜在问题是ActiveSheet可能并不总是"Sheet1"，而且已知InStr也会引起一些与vbTextCompare参数有关的问题.使用给定的代码，我将其更改为以下内容:

Ah yeah that's cake I do it all day long. Actually your code looks pretty much like the way I'd do it. Although, I opt to use looping through integers as opposed to using the "For Each" method. The only potential problems I can see with your code is that ActiveSheet may not always be "Sheet1", and also InStr has been known to give some issues regarding the vbTextCompare parameter. Using the given code, I would change it to the following:

Sub compare_cols()

    'Get the last row
    Dim Report As Worksheet
    Dim i As Integer, j As Integer
    Dim lastRow As Integer

    Set Report = Excel.Worksheets("Sheet1") 'You could also use Excel.ActiveSheet _
                                            if you always want this to run on the current sheet.

    lastRow = Report.UsedRange.Rows.Count

    Application.ScreenUpdating = False

    For i = 2 To lastRow
        For j = 2 To lastRow
            If Report.Cells(i, 1).Value <> "" Then 'This will omit blank cells at the end (in the event that the column lengths are not equal.
                If InStr(1, Report.Cells(j, 2).Value, Report.Cells(i, 1).Value, vbTextCompare) > 0 Then
                    'You may notice in the above instr statement, I have used vbTextCompare instead of its numerical value, _
                    I find this much more reliable.
                    Report.Cells(i, 1).Interior.Color = RGB(255, 255, 255) 'White background
                    Report.Cells(i, 1).Font.Color = RGB(0, 0, 0) 'Black font color
                    Exit For
                Else
                    Report.Cells(i, 1).Interior.Color = RGB(156, 0, 6) 'Dark red background
                    Report.Cells(i, 1).Font.Color = RGB(255, 199, 206) 'Light red font color
                End If
            End If
        Next j
    Next i

    'Now I use the same code for the second column, and just switch the column numbers.
    For i = 2 To lastRow
        For j = 2 To lastRow
            If Report.Cells(i, 2).Value <> "" Then
                If InStr(1, Report.Cells(j, 1).Value, Report.Cells(i, 2).Value, vbTextCompare) > 0 Then
                    Report.Cells(i, 2).Interior.Color = RGB(255, 255, 255) 'White background
                    Report.Cells(i, 2).Font.Color = RGB(0, 0, 0) 'Black font color
                    Exit For
                Else
                    Report.Cells(i, 2).Interior.Color = RGB(156, 0, 6) 'Dark red background
                    Report.Cells(i, 2).Font.Color = RGB(255, 199, 206) 'Light red font color
                End If
            End If
        Next j
    Next i

Application.ScreenUpdating = True

End Sub

我做的事情与众不同:

1. 我使用了上述整数方法(与"for each"方法相对).
2. 我将工作表定义为对象变量.
3. 我在InStr函数中使用了vbTextCompare而不是其数值.
4. 我添加了一个if语句来省略空白单元格.提示:即使只有一个工作表中的列过长(例如，单元格D5000偶然格式)，则所有列的usedrange被认为是5000.
5. 我为颜色使用了rgb代码(对我来说这更容易，因为我在这个小隔间里有一张备忘单钉在我旁边的墙上哈哈).

好吧，总结一下.祝您的项目好运！

Well that about sums it up. Good luck with your project!

这篇关于VBA宏可比较两列并以颜色突出显示单元格差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！