时间戳

首页 > 解决方案 > 如何使用 VBA 比较两列并突出显示差异？

excel - 如何使用 VBA 比较两列并突出显示差异？

问题描述

 Sub Macro13()
 '
 ' Macro13 Macro
 '

 '

Columns("B:C").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$F2=$G2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).StopIfTrue = False

Columns("D:E").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$D2=$E2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).StopIfTrue = False

Columns("F:G").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$F2=$G2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).StopIfTrue = False



End Sub

当我这样做时，它似乎是条件格式，但我无法弄清楚它的突出显示。它绝对不是突出不同的价值观。

仔细查看似乎我有正确的公式和单元格范围，但是我只是看不到我哪里出错了。

谢谢

标签： excelvba

解决方案

比较两列并突出显示差异

Sub Highlight()

        Dim n As Integer
        Dim valE As Double
        Dim valI As Double
        Dim i As Integer

        n = Worksheets("Sheet1").Range("E:E").Cells.SpecialCells(xlCellTypeConstants).Count
        Application.ScreenUpdating = False

        For i = 2 To n
        valE = Worksheets("Indices").Range("E" & i).Value
        valI = Worksheets("Indices").Range("I" & i).Value

            If valE = valI Then

            Else:

               Worksheets("Sheet1").Range("E" & i).Font.Color = RGB(255, 0, 0)

            End If
        Next i

    End Sub

第二种解决方案

在此处输入图像描述

Sub CompareColumns()

Dim aRng, bRng As Range
Set aRng = Range("A2:A20")
Set bRng = Range("B2:B6")

For Each aCell In aRng
    For Each bCell In bRng
        If aCell Is Nothing Or bCell Is Nothing Then
        ElseIf aCell.Text = bCell.Text Then
            aCell.Font.Color = bCell.Font.Color
            aCell.Interior.Color = bCell.Interior.Color
        Else
        End If
    Next bCell
Next aCell

End Sub

推荐阅读