CODEWITHSUNDEEP
vbaexcel
Katherine Reed
Katherine Reed

Reputation: 301

Changing color of cell based on color of different cell

I found Excel VBA code online that highlights unique values in a row in excel. My table has about a hundred rows, and the code checks each row for unique values. I have tweaked the code a little bit for my purposes, so the code now just changes the font color of the unique values. The code works perfectly, but I want to add an additional element to the code, and I'm not sure how to do it.

img

You can see in this partial photo of my table the red unique numbers. However, the code is set up to only search for unique values from the third column in the photo to the last column (the column containing all 1s is its own column). What I want to add to the code is that after a unique value is found in a row and the font color of that value is changed, I want the cell in the first column of that row to be highlighted yellow.

For example, I know that there is a unique value in the first row because the last value in that row is red. Now that a unique value in the first row has been found, I want the first cell in that row to be highlighted yellow. However, in the fifth row of my table, there are no unique values and so none of the values in that row are red, so I don't want anything about the first cell in that row to change.

Does anyone have any ideas about how to change my code in order to produce the desired results?

Thank you in advance.

The current code:

Option Explicit

Sub ColorUniquesInEachRow()

    Dim LastRow As Long
    Dim LastColumn As Long
    Dim RowIndex As Long

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For RowIndex = 6 To LastRow
        LastColumn = Cells(RowIndex, Columns.Count).End(xlToLeft).Column

        With Range(Cells(RowIndex, 4), Cells(RowIndex, LastColumn))
            .FormatConditions.AddUniqueValues
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).DupeUnique = xlUnique
            With .FormatConditions(1).Font
                .ColorIndex = 3
                .Bold = True
            End With
            .FormatConditions(1).StopIfTrue = False
        End With

    Next

End Sub

Upvotes: 0

Views: 371

Answers (1)

DisplayName
DisplayName

Reputation: 13386

I'd avoid conditional formatting since it can quickly run out of control

furthermore cells formatted by conditional formatting need a special way of getting spotted

so I propose you the following code:

Option Explicit

Sub ColorUniquesInEachRow()
    Dim LastColumn As Long
    Dim RowIndex As Long
    Dim cell As Range
    Dim unique As Boolean

    For RowIndex = 6 To Cells(Rows.count, 1).End(xlUp).Row
        LastColumn = Cells(RowIndex, Columns.count).End(xlToLeft).Column

        unique = False

        With Range(Cells(RowIndex, 4), Cells(RowIndex, LastColumn))

            For Each cell In .SpecialCells(xlCellTypeConstants)
                If WorksheetFunction.CountIf(.Cells, cell.Value) = 1 Then
                    With cell.Font
                        .ColorIndex = 3
                        .Bold = True
                    End With
                    unique = True
                End If
            Next

            If unique Then Cells(RowIndex, 1).Interior.ColorIndex = 6

        End With
    Next
End Sub

Upvotes: 3

Related Questions