Highlight duplicate values ignoring first Occurrence value in Excel

[HappyWilliam]

Hello Everyone,

Please can someone help me figure this out. Would like a formula/vba code for my conditional Formatting to highlight duplicates within a range but ignoring first occurrences

Check A	Check B	Check C	Check D	Check E	Check F	Check G	Check H
2	15	23	8	14	21	22	38
4	13	48	56	45	2	65	70
10	11	56	42	32	37	52	99
22	53	7	47	50	59	67	90
55.5	2	45	100	115	145	73	48
99	88	59	13	33	39	49	50
4	55.5	70	51	81	64	120	115
2	11	40	52	25	35	99	65

thanks in advance

 

[HappyWilliam]

Thanks, yes slightly you are on the right track. Some values was missed out like 115(F8) and 115(I10) 55.5,
and 56 (E5) is the first occurence. It would need a little tweak

 

[Peter_SSs]

Some values was missed out like 115(F8) and 115(I10) 55.5

Yes, I was missing a $ sign - see correction below.

and 56 (E5) is the first occurence

The first 56 would be in D6 if you are checking by columns as you indicated earlier.

23 05 29.xlsm
	A	B	C	D	E	F	G	H	I
3		Check A	Check B	Check C	Check D	Check E	Check F	Check G	Check H
4		2	15	23	8	14	21	22	38
5		4	13	48	56	45	2	65	70
6		10	11	56	42	32	37	52	99
7		22	53	7	47	50	59	67	90
8		55.5	2	45	100	115	145	73	48
9		99	88	59	13	33	39	49	50
10		4	55.5	70	51	81	64	120	115
11		2	11	40	52	25	35	99	65
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:I11	Expression	=COUNTIF($A$4:A$11,B4)+COUNTIF(B$4:B4,B4)>1	text	NO

 

[HappyWilliam]

Thanks to Everyone

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[mohadin]

Would like a formula/vba code

Tow VBA options
In a module

Sub test2()
    Dim cel As Range
    Dim i&
    Cells(1).CurrentRegion.Interior.Color = vbWhite
    With CreateObject("scripting.dictionary")
    For i = 1 To 8
        For Each cel In Range(Cells(2, i), Cells(9, i))
            If Not .exists(cel.Value) Then
                .Add cel.Value, ""
            Else
                cel.Interior.Color = vbRed
            End If
        Next: Next
    End With
End Sub

Worksheet code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    Dim i&
    If Not Intersect(Target, Cells(1).CurrentRegion) Is Nothing Then
    Cells(1).CurrentRegion.Interior.Color = vbWhite
    With CreateObject("scripting.dictionary")
        For i = 1 To 8
            For Each cel In Range(Cells(2, i), Cells(9, i))
                If Not .exists(cel.Value) Then
                    .Add cel.Value, ""
                Else
                    cel.Interior.Color = vbRed
                End If
            Next: Next
    End With
    End If
End Sub