CODEWITHSUNDEEP
vbaexcel
Samadhan Gaikwad
Samadhan Gaikwad

Reputation: 196

Find duplicates in a coulmn and highlight row background color with alternate color

Initial content

Processed content

  1. I want to highlight cell colors of rows (text is in column "A" to "G")with same color when value in "A" column is same and want to iterate same operation for all rows & apply alternate colors.
  2. Also want to change font color to Red in 2 cells (of column "F" and "G") when there is text "Files are on EMEA server" in "F" column.

Upvotes: 2

Views: 1278

Answers (1)

Wolfie
Wolfie

Reputation: 30047

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

Before:

before

After:

after

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.

Upvotes: 2

Related Questions