Highlight duplicate values of certain columns only

[sujittalukde]

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.

[mikerickson]

Select B1.
Set Conditional Formatting to the formula

=((COUNTIF($B:$B,B1)+(COUNTIF($G:$G,B1)+(COUNTIF($L:$L,B1)+(COUNTIF($Q:$Q,B 1)+(COUNTIF($V:$V,B1)+(COUNTIF($AA:$AA,B1))>1)

And copy the formatting to all the cells in columns B,G,L,Q,V & AA in your data set.

[Bob Phillips]

Few too many brackets in Mike's formula

=(COUNTIF($B:$B,B1)+COUNTIF($G:$G,B1)+COUNTIF($L:$L,B1)+COUNTIF($Q:$Q,B1)+
COUNTIF($V:$V,B1)+COUNTIF($AA:$AA,B1))>1

You can even shorten it

=SUMPRODUCT((MOD(COLUMN(B2:AA200),5)=2)*(B2:AA200<>"")*(B2:AA200=B2))>1

I would trap it on input though using events



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:AA" '<== change to suit
Dim mpFormula As String

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Column Mod 5 = 2 Then
mpFormula = "=SUMPRODUCT((MOD(COLUMN(B2:AA200),5)=2)*" & _
"(B2:AA200<>"""")*(B2:AA200=" & .Address & "))"
If Me.Evaluate(mpFormula) > 1 Then
MsgBox "duplicate"
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[sujittalukde]

thanks xld your macro code is working. I have not tried the formulaE as the macro is doing well.