Excel: How to color cells based on color of other cells having same value in a range?

Question

I have a range / table of say 10 rows to 3 columns.

Any particular cell can have any particular value from the list created using Data validation. All the cells in this range have the same data validation list.

If a value repeats across any row or across any column, not diagonally or otherwise, then those values are highlighted using conditional formatting.

Now I want that if the same value is present elsewhere in non-duplicate row or column, then those cells should be colored too. (Preferably a different color than one used for conditional formatting to know the difference between both).

The purpose is to know which values are repeated and how, and where in the range those values are used but are not repeated as per the criteria.

P.s.: Please tell if additional information or some clarification is required.

Refer the attached image to understand my query better. The blue ones are colored through conditional formatting and the green ones are needed to be colored through your help.

Image for understanding

MarcinSzaleniec MarcinSzaleniec · Accepted Answer · 2017-10-11T09:34:42

You could use conditional formatting->Duplicate Values for the whole range, with different color and set up sequence in Conditional Formatting->Manage Rules. No need of VBA in my opinion.

EDIT: Ok, I think I know what are you asking for. Try this little subroutine:

Sub PaintDuplis()
Dim rng As Range
Dim col As Range
Dim row As Range
Dim cl As Range, cl2 As Range

Set rng = Range("B4:D11") 'or whatever your range is.
'Columns
For Each col In rng.Columns
  For Each cl In col.Cells
    If WorksheetFunction.CountIf(col, cl.Value) > 1 Then cl.Interior.Color =    vbYellow
  Next cl
Next col

'Rows
For Each row In rng.rows
  For Each cl In row.Cells
    If WorksheetFunction.CountIf(row, cl.Value) > 1 Then cl.Interior.Color = vbYellow
  Next cl
Next row

'Paint whole range
For Each cl In rng
  If cl.Interior.Color = vbYellow Then
    For Each cl2 In rng
        If cl2.Value = cl.Value And cl2.Interior.Color <> vbYellow Then cl2.Interior.Color = vbRed
    Next cl2
  End If
Next cl

I have given up conditional formatting, instead I have used VBA to paint duplicates in columns/rows and then painted all still white cells with red if it equals to one already in yellow. Hope it helped.

Excel: How to color cells based on color of other cells having same value in a range?

1 Answers