在同伴中查找重复项，并用其他颜色突出显示行背景颜色 [英] Find duplicates in a coulmn and highlight row background color with alternate color

问题描述

1. 我要突出显示行的单元格颜色(文本在"A"列中当"A"列中的值相同并且想要对所有行重复相同的操作&应用其他颜色.
2. 还希望在2个单元格("F"列和在"F"列中显示文本文件在EMEA服务器上"时显示为"G".

推荐答案

此宏在两种颜色之间交替，当A列中的值更改时更改.

This macro alternates between 2 colours, changing when the value in column A changes.

Sub Highlighting()

Dim rw As Long
Dim lastrw As Long

' Define 2 different highlighting colours by their RGB values
Dim col1 As Long
Dim col2 As Long
col1 = RGB(255, 230, 180)
col2 = RGB(180, 230, 255)
' "Current" colour for highlighting
Dim col As Long
col = col1

With ThisWorkbook.ActiveSheet
    ' Get last row by last non-empty cell in column A
    lastrw = .Cells(.Rows.Count, 1).End(xlUp).Row
    ' Cycle through column A
    For rw = 1 To lastrw
        ' Highlight row with current colour
        .Range("A" & rw & ":G" & rw).Interior.Color = col
        ' If column A value in next row is different, change colour
        If .Cells(rw + 1, 1).Value <> .Cells(rw, 1) Then
            ' Set to whichever colour it is not
            If col = col1 Then
                col = col2
            Else
                col = col1
            End If
        End If
    Next rw
End With

End Sub

之前:

之后:

在某些情况下，您可以通过插入自己的条件来将字体颜色更改为红色. If .Range("F"& rw).Value ="Files ..." 等等进入主 For 循环.

You should be able to change the font colour to red in certain cases, by inserting your own condition If .Range("F" & rw).Value = "Files ... " etc into the main For loop.

这篇关于在同伴中查找重复项，并用其他颜色突出显示行背景颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！