Red font colour part code,if repeat more than one

[PRADEEPB270]

I am looking for a VBA codes working who put up the part code in red font colour also bold if it appears more than one.

Please refer my attach file,in this file,Column 'B' having part codes but one part code may be appear one or more than one.
If any part appear more than once then the result should be as in column "F".How is it possible?

[Rudi]

Hi Pradeep,

It's been a while

You can use conditional formatting to do this as it will be a lot more optimal that VBA.
See the image and sample workbook for an illustration.

1.jpg

[PRADEEPB270]

Yes,I was busy in my company's audit activities.
Thanks Rudi for your nice suggestion.
But Rudi,I want to know the solution through VBA Codes.Is it possible? If yes,please help me.

[Rudi]

OK...I will set up the code on your sample.

[Rudi]

Hi,

Here is the code. Modify the variables to ensure that the ranges work on your sheet...

Code: Select all

Sub RepeatingCodes()
Dim lRow As Long
    With Range("F5:G5", Range("F" & Rows.Count).End(xlUp))
        lRow = .Rows.Count + 4
        .FormatConditions.Delete
        .FormatConditions.Add _
            Type:=xlExpression, Formula1:="=COUNTIF($B$5:$B$" & lRow & ",$B5)>1"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Font
            .Bold = True
            .Color = vbRed
        End With
    End With
End Sub

[Rudi]

Hi,

BTW: If you only want the duplicate marked in bold/red *without* the original being highlighted either, use this code variation:

Code: Select all

Sub RepeatingCodes()
Dim lRow As Long
    With Range("F5:G5", Range("F" & Rows.Count).End(xlUp))
        lRow = .Rows.Count + 5 - .Rows.Count
        .FormatConditions.Delete
        .FormatConditions.Add _
            Type:=xlExpression, Formula1:="=COUNTIF($B$5:$B" & lRow & ",$B5)>1"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Font
            .Bold = True
            .Color = vbRed
        End With
    End With
End Sub

[PRADEEPB270]

Thanks Rudi for help.Codes are working as desired.