Change some font colour from black to white

[motilulla]

Hello,</SPAN></SPAN>

The code below do highlight duplicates in various colours as shown below</SPAN></SPAN>

Sub Find_Duplicate_Entry()
    Dim Cel As Variant
    Dim myrng As Range
    Dim clr As Long
    Set myrng = Range("D6:D" & Range("D65536").End(xlUp).Row)
    myrng.Interior.ColorIndex = xlNone
    myrng.Font.ColorIndex = 1
    clr = 3
    For Each Cel In myrng
        If Application.WorksheetFunction.CountIf(myrng, Cel) > 1 Then
            If WorksheetFunction.CountIf(Range("D6:D" & Cel.Row), Cel) = 1 Then
                Cel.Interior.ColorIndex = clr
                If clr = 6 Then clr = 3
                clr = clr + 1
            Else
                Cel.Interior.ColorIndex = myrng.Cells(WorksheetFunction.Match(Cel.Value, myrng, False), 1).Interior.ColorIndex
            End If
        End If
    Next
End Sub

Book1
	C	D	E
1
2
3
4
5
6		0
7		0
8		0
9		0
10		0
11		0
12		0
13		1
14		1
15		1
16		1
17		2
18		2
19		3
20		4
21		5
22		5
23		6
24		7
25		7
26		7
27		7
28		7
29		7
30		22
31		22
32		22
33		23
34		24
35		25
36		26
37		26
38		178
39		179
40		179
41		180
42		180
43		180
44		180
45		180
46		183
47		183
48		183
49		183
50		183
51		184
52		184
53		184
54		184
55		184
56		184
57		184
58		245
59		245
60		245
61		246
62		246
63		246
64		247
65		247
66		247
67		247
68		247
69		248
70		248
71		248
72		248
73		248
74		249
75		249
76		249
77		249
78		249
79		249
80		474
81		474
82		474
83		474
84		525
85		525
86		525
87		525
88		525
89		570
90		571
91		571
92		572
93		573
94		573
95
96
Sheet1

I would like to get change font colour white in the Red & Blue background as shown below. Please need help someone can modified the above code or make a new one</SPAN></SPAN>

Like this</SPAN>

Book1
	C	D	E
1
2
3
4
5
6		0
7		0
8		0
9		0
10		0
11		0
12		0
13		1
14		1
15		1
16		1
17		2
18		2
19		3
20		4
21		5
22		5
23		6
24		7
25		7
26		7
27		7
28		7
29		7
30		22
31		22
32		22
33		23
34		24
35		25
36		26
37		26
38		178
39		179
40		179
41		180
42		180
43		180
44		180
45		180
46		183
47		183
48		183
49		183
50		183
51		184
52		184
53		184
54		184
55		184
56		184
57		184
58		245
59		245
60		245
61		246
62		246
63		246
64		247
65		247
66		247
67		247
68		247
69		248
70		248
71		248
72		248
73		248
74		249
75		249
76		249
77		249
78		249
79		249
80		474
81		474
82		474
83		474
84		525
85		525
86		525
87		525
88		525
89		570
90		571
91		571
92		572
93		573
94		573
95
96
Sheet1-1

Thank you all</SPAN></SPAN>
Excel 2000</SPAN></SPAN>
Regards,</SPAN>
Moti</SPAN>

 

[Rick Rothstein]

The code below do highlight duplicates in various colours as shown below

Sub Find_Duplicate_Entry()
    Dim Cel As Variant
    Dim myrng As Range
    Dim clr As Long
    Set myrng = Range("D6:D" & Range("D65536").End(xlUp).Row)
    myrng.Interior.ColorIndex = xlNone
    myrng.Font.ColorIndex = 1
    clr = 3
    For Each Cel In myrng
        If Application.WorksheetFunction.CountIf(myrng, Cel) > 1 Then
            If WorksheetFunction.CountIf(Range("D6:D" & Cel.Row), Cel) = 1 Then
                Cel.Interior.ColorIndex = clr
                If clr = 6 Then clr = 3
                clr = clr + 1
            Else
                Cel.Interior.ColorIndex = myrng.Cells(WorksheetFunction.Match(Cel.Value, myrng, False), 1).Interior.ColorIndex
            End If
        End If
        Cel.Font.Color = -vbWhite * (77 * (Cel.Interior.Color Mod &H100) + 151 * ((Cel.Interior.Color \ &H100) Mod &H100) + 28 * ((Cel.Interior.Color \ &H10000) Mod &H100) < 32640)
    Next
End Sub

Add the line of code I show in red above (it will automatically choose the correct black or white font color to use no matter what background colors you use to color your cells).

 

[motilulla]

Add the line of code I show in red above (it will automatically choose the correct black or white font color to use no matter what background colors you use to color your cells).

Thank you so much Rick, adding a just one line you made my life easer. It has been great help for me </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>