Conditional formatin per column

[Bejbas]

Hello everyone,


I have got problem with conditional formatting.
In Column A and B a have got some values.
I'm looking for some way to hightlight duplicates values per columns. Of course I can create manually rules per column but finally file will be have over 1000 columns.
I need range where excel will be highlight duplicates.

For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]grapes[/TD]
[TD]grapes[/TD]
[/TR]
[TR]
[TD]mango[/TD]
[TD]coconut[/TD]
[/TR]
[TR]
[TD]melon[/TD]
[TD]pear[/TD]
[/TR]
[TR]
[TD]pineapple[/TD]
[TD]peach[/TD]
[/TR]
[TR]
[TD]pear[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]grapes[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Below screen:
https://imgur.com/4Vq9kKT


Thanks in advance.

 

[James006]

Hello,

You can use the following formula:

=COUNTIF(A2:A7,A2)>1

Hope this will help

 

[Bejbas]

With all due respect but it doesn't work. Maybe I explain not enough cleary.
I recorded macro which highlight duplicates values but I have no idea what can I do to add range not one column.

Sub Macro4()'
' Macro4 Macro
'


'
    Columns("A:A").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("B:B").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[James006]

Hello,

Just tested formula on 10 Columns ... and it does work ... :wink:

We agree that after selecting you whole range from cell A2 to the very last cell of your sheet...

you add conditional formatting ... with this custom formula ...

HTH

 

[Bejbas]

Working perfect!
Thanks!

 

[James006]

You are welcome ... :wink:

Pleased to hear everything is OK ...