I have a Range("J15:AA15") containing numbers.
What I wish to do is create a macro to detect duplicates.
Using
I would like the result to be like
Any help would be appreciated.
Thanks.
How do I write a macro to Conditional Format a cell range?
-
- 3StarLounger
- Posts: 357
- Joined: 02 Jun 2013, 05:55
- Location: Australia - SOUTH MORANG - A northern suburb of the city of MELBOURNE in the state of Victoria
How do I write a macro to Conditional Format a cell range?
You do not have the required permissions to view the files attached to this post.
George
When we're gaun up the hill o’ fortune, may we ne'er meet a frien' comin' doun!
(When we are going up the hill of fortune, may we never meet a friend coming down!)
Don't cry because it's over...Smile because it happened.l
At the end of the day it's midnight.
When we're gaun up the hill o’ fortune, may we ne'er meet a frien' comin' doun!
(When we are going up the hill of fortune, may we never meet a friend coming down!)
Don't cry because it's over...Smile because it happened.l
At the end of the day it's midnight.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: How do I write a macro to Conditional Format a cell rang
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?
Conditional formatting will dynamically highlight duplicates if it is set on the range you refer to?
Please explain the reason for a macro request?
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How do I write a macro to Conditional Format a cell rang
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
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: How do I write a macro to Conditional Format a cell rang
...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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 3StarLounger
- Posts: 357
- Joined: 02 Jun 2013, 05:55
- Location: Australia - SOUTH MORANG - A northern suburb of the city of MELBOURNE in the state of Victoria
Re: How do I write a macro to Conditional Format a cell rang
Hans and Rudi.
Thanks for your replies.
The reason for a macro request was for automation.
Thanks for your replies.
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.?Rudi wrote: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?
The reason for a macro request was for automation.
George
When we're gaun up the hill o’ fortune, may we ne'er meet a frien' comin' doun!
(When we are going up the hill of fortune, may we never meet a friend coming down!)
Don't cry because it's over...Smile because it happened.l
At the end of the day it's midnight.
When we're gaun up the hill o’ fortune, may we ne'er meet a frien' comin' doun!
(When we are going up the hill of fortune, may we never meet a friend coming down!)
Don't cry because it's over...Smile because it happened.l
At the end of the day it's midnight.
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How do I write a macro to Conditional Format a cell rang
Conditional formatting is saved with the workbook...
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 357
- Joined: 02 Jun 2013, 05:55
- Location: Australia - SOUTH MORANG - A northern suburb of the city of MELBOURNE in the state of Victoria
Re: How do I write a macro to Conditional Format a cell rang
Thanks Hans.HansV wrote:Conditional formatting is saved with the workbook...
George
When we're gaun up the hill o’ fortune, may we ne'er meet a frien' comin' doun!
(When we are going up the hill of fortune, may we never meet a friend coming down!)
Don't cry because it's over...Smile because it happened.l
At the end of the day it's midnight.
When we're gaun up the hill o’ fortune, may we ne'er meet a frien' comin' doun!
(When we are going up the hill of fortune, may we never meet a friend coming down!)
Don't cry because it's over...Smile because it happened.l
At the end of the day it's midnight.