MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Highlight duplicate values of certain columns only

  Asked By: Chisisi    Date: Jan 14    Category: MS Office    Views: 856

I want highlight duplicate values for certain columns
I have data from column A to Column AD but I want to highlight
duplicate values for these columns:
Column B, Column G, Column L, Column Q, Column V, Column AA.

If any value appears more than once in any these column then highlight
those duplicate values.
For eg if value 10 appears in column B10 and also at cell L15 then
highlight both these values. The code should ignore other columns ie
if value 10 also appears in column C then value in column C should not
be highlighted.



1 Answer Found

Answer #1    Answered By: Tyler Thompson     Answered On: Jan 14

A macro like that should solve this problem.
Remember to change columnsToCheck to cover all your columns. I'm
covering only B, G and L. Also check the startRow variable.

Sub HighlightValues()

Dim columnsToCheck(2) As Long
Dim startRow As Long
Dim i, j, k, l As Long

columnsToCheck(0) = 2
columnsToCheck(1) = 7
columnsToCheck(2) = 12
startRow = 1

For i = 0 To UBound(columnsToCheck) Step 1
k = startRow
While Cells(k, columnsToCheck(i)) <> ""
For j = 0 To UBound(columnsToCheck) Step 1
l = startRow
While Cells(l, columnsToCheck(j)) <> ""
If Cells(k, columnsToCheck(i)).Value = Cells(l,
columnsToCheck(j)).Value Then
If (columnsToCheck(i) <> columnsToCheck(j)) Or
(columnsToCheck(i) = columnsToCheck(j) And k <> l) Then
Cells(l, columnsToCheck(j)).Interior.ColorIndex = 3
End If
End If
l = l + 1
Next j
k = k + 1
Next i

End Sub

Didn't find what you were looking for? Find more on Highlight duplicate values of certain columns only Or get search suggestion and latest updates.