Macro to sort Ascending,identify duplicates& highlight yellow

[Doodle80]

HI,

Trying to build a macro- to sort the list(ascending) of numbers in column A and identify duplicates and highlight them yellow. Only catch to this is one month the list might have 100 cells information, next month it only be 50.

I am guessing it will incorporate if then else statement(for identifying duplicates) and relative range to do both sorting.

Any suggestions is greatly appreciated.

[hardlife]

Hi, You can try this, firstly mark column A = select, then set Format, Conditional formating, use formula, set custom format.

formula:

=COUNTIF(A:A,A1)>1

=COUNTIF(A:A;A1)>1 country oriented, works in Czech Republic

http://www.mrexcel.com/articles/dupl...formatting.php

GOOD LUCK AND GOOD NIGHT

Pavel Humenuk

[hardlife]

Sub SortAndMarkDuplicatesInColumnA()

Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Columns("A:A").Select
'Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(A:A,A1)>1" 'for our country Czech Republic use "=COUNTIF(A:A;A1)>1"
Selection.FormatConditions(1).Interior.ColorIndex = 27

End Sub

GOOD LUCK AND GOOD NIGHT

Pavel Humenuk

[Doodle80]

Thanks for helping-

1) Conditional formatting is not doing anything-Followed step by step procedure.

2) Tried Macro - IT SAYS - Compile Error:
Expected:Expression

Sorry little new @ this.

Thanks Again.

[mdmackillop]

Try this version of Hardlife's code
[VBA]
Option Explicit
Sub SortAndMarkDuplicatesInColumnA()
Range("A1").Select
With Columns("A:A")
.Sort Key1:=Range("A2"), Order1:=xlAscending
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF(A:A,A1)>1"
.FormatConditions(1).Interior.ColorIndex = 27
End With
End Sub

[/VBA]

[mdmackillop]

Pavel,
Problem I think is separator in "=COUNTIF(A:A;A1)>1". Comma instead of semi-colon.

[Doodle80]

Thanks Guys it really worked!! here is another question what if i want to extended the sorting to column J as Column A Numbers have information extended till column J.

I am trying to learn about Range- eventually wanna sort column A to J and highlight the duplicates.

Thanks a lot..

[Bob Phillips]

[vba]

Option Explicit
Sub SortAndMarkDuplicatesInColumnA()
Range("A1").Select
With Columns("A:J")
.Sort Key1:=Range("A2"), Order1:=xlAscending
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF($A:$A,$A1)>1"
.FormatConditions(1).Interior.ColorIndex = 27
End With
End Sub
[/vba]

[hardlife]

It is beautiful to see clear code, Thanks a lot to Everybody,
It is also beautiful to see help pro users to us,

mdmackillop, xld and Doodle80, all other users,
wish You to have a nice day,
Pavel Humenuk

[Doodle80]

Thanks everyone!!

[mdmackillop]

Hi Bob,
Out of interest, can we set the Conditional Formatting without selecting A1 first? My attempts were unsuccessful.
Regards
Malcolm

[hardlife]

=COUNTIF($A$2:$A$10,A2)>1

happy and sunny winter day to everybody

Pavel Humenuk

[mdmackillop]

That gives me
=COUNTIF($A$2:$A$10,IT65535)>1
or using $A2 I get
=COUNTIF($A$2:$A$10,$A65535)>1

[Bob Phillips]

Yes you can, but you have to adjust it according to what is selected.

For instance, suppose you want a formula of =A1>7 in the range A1:E10, this will work wherever you are

[vba]

'just to prove it works
Range("M17").Select
With Range("A1:E10")

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & ActiveCell.Address(False, False) & ">7"
.FormatConditions(1).Interior.ColorIndex = 3
End With
[/vba]

[Bob Phillips]

In the example in this thread, it would be

[vba]

Sub SortAndMarkDuplicatesInColumnA()
Dim mpFormula As String

With Columns("A:J")
.Sort Key1:=Range("A2"), Order1:=xlAscending
.FormatConditions.Delete
mpFormula = "=COUNTIF($A:$A,$" & ActiveCell.Address(False, False) & ")>1"
.FormatConditions.Add Type:=xlExpression, Formula1:=mpFormula
.FormatConditions(1).Interior.ColorIndex = 27
End With
End Sub
[/vba]

[mdmackillop]

Thanks Bob, I'll have a play with that method.

[hardlife]

May be me is wrong, but it happened to me in office2007,
if me pasted formula, conditional formating was set wrongly,
so me edited it again and it was OK, if it was the question.

when it is not working select A2:A10 and
edit stored conditional formating again,
Pavel

=COUNTIF($A$2:$A$10,A2)>1

That gives me
=COUNTIF($A$2:$A$10,IT65535)>1
or using $A2 I get
=COUNTIF($A$2:$A$10,$A65535)>1