How do I write a macro to Conditional Format a cell range?

[geecee]

I have a Range("J15:AA15") containing numbers.

What I wish to do is create a macro to detect duplicates.

Using

ScreenShot042.jpg

I would like the result to be like

ScreenShot043.jpg

Any help would be appreciated.

Thanks.

[Rudi]

Why do you want a macro?
Conditional formatting will dynamically highlight duplicates if it is set on the range you refer to?

Please explain the reason for a macro request?

[HansV]

I recorded a macro, then cleaned it up a bit:

Code: Select all

Sub HighlightDuplicates()
    With Range("J15:AA15").FormatConditions
        .Delete
        .AddUniqueValues
        With .Item(1)
            .DupeUnique = xlDuplicate
            .Font.Color = -16383844
            .Interior.Color = 13551615
            With .Borders(xlLeft)
                .LineStyle = xlContinuous
                .Color = -16383844
                .Weight = xlThin
            End With
            With .Borders(xlRight)
                .LineStyle = xlContinuous
                .Color = -16383844
                .Weight = xlThin
            End With
            With .Borders(xlTop)
                .LineStyle = xlContinuous
                .Color = -16383844
                .Weight = xlThin
            End With
            With .Borders(xlBottom)
                .LineStyle = xlContinuous
                .Color = -16383844
                .Weight = xlThin
            End With
        End With
    End With
End Sub

[Rudi]

    
...and this is Hans's macro taken to the cleaners.
I had some time to spare (and felt like experimenting with it!)

Spoiler

Code: Select all

Sub HighlightDuplicates()
    With Range("J15:AA15").FormatConditions
        .Delete
        .AddUniqueValues
        With .Item(1)
            .DupeUnique = xlDuplicate
            .Font.Color = -16383844
            .Interior.Color = 13551615
            .Borders(xlLeft).Color = -16383844
            .Borders(xlRight).Color = -16383844
            .Borders(xlTop).Color = -16383844
            .Borders(xlBottom).Color = -16383844
        End With
    End With
End Sub

[HansV]

Thanks, Rudi.

[geecee]

Hans and Rudi.

Thanks for your replies.

Why do you want a macro?
Conditional formatting will dynamically highlight duplicates if it is set on the range you refer to?

Please explain the reason for a macro request?

If I use Conditional Formatting by clicking Conditional Formatting>Highlight Cell Rules>Duplicate Values>Red Border yes that's OK but can I save it for use the next time I want to use it.?

The reason for a macro request was for automation.

[HansV]

Conditional formatting is saved with the workbook...

[geecee]

Conditional formatting is saved with the workbook...

Thanks Hans.