PDA

View Full Version : Help required - alter VBA code



satish gubbi
02-08-2012, 07:29 AM
Hi

I found the below code which will color the duplicates, kindly help me to add below condition

After:=ActiveCell _
, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False


into the code, so that each cell in column C will be considered for finding duplicates from the begining to end of the column and highlight the Cell even value found with or without suffix or prefix's


Sub Highlight_Duplicates()
Dim Rng As Range, MyCell As Range
Set Rng = Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If Application.WorksheetFunction.CountIf(Rng, MyCell) > 1 Then
Range(MyCell.Address & ":" & "C" & MyCell.Row).Interior.ColorIndex = 6
Else
MyCell.EntireRow.Interior.ColorIndex = xlNone
End If
Next MyCell
End Sub


Kindly help

Bob Phillips
02-08-2012, 08:13 AM
Is this what you mean?




Sub Highlight_Duplicates()
Dim Rng As Range, MyCell As Range
Dim lastrow As Long

lastrow = Range("C" & Rows.Count).End(xlUp).Row
Set Rng = Range("C1:C" & lastrow)
For Each MyCell In Rng

If Application.CountIf(Range(MyCell, Cells(lastrow, MyCell.Column)), MyCell) > 1 Then

Range(MyCell.Address & ":" & "C" & MyCell.Row).Interior.ColorIndex = 6
Else

MyCell.EntireRow.Interior.ColorIndex = xlNone
End If
Next MyCell
End Sub

satish gubbi
02-08-2012, 09:10 AM
Hi Xld,
Thank you very much for your reply, however this code is not highlighting all the duplicate values. I expected this code to highlight all the cells that has duplicates
attached excel file has highlighted values with duplicates, kindly help me getting this code worked as intended

each cell content has to be tested for duplicate from begining to end and should be all cells should be colored

Bob Phillips
02-08-2012, 09:22 AM
Why should rows 6 and 36 be highlighted?

satish gubbi
02-08-2012, 09:29 AM
Row 6, 15, 36 and 47 has the same value except the prefix's, if cell value 47 has been considered for finding the duplicates, it will show rest three rows (6,15 & 36) as found with match case untick

Bob Phillips
02-08-2012, 09:57 AM
Use conditional formatting with a formula of

=SUMPRODUCT(--(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"FW:",""),"RE:",""))=TRIM(SUBSTITUTE(SUBSTITUTE($A$2:$A$51,"FW:",""),"RE:",""))))

satish gubbi
02-09-2012, 04:47 AM
Hi xld,

thank you very much for reply, however I am getting an error stating

"You may not use unions, intersections, or array constants for conditional formatting criteria."

kindly help

Bob Phillips
02-09-2012, 04:51 AM
The forum is embedding a space in one of the SUBSTITUTE function names, remove it. I also forgot o add >1 at the end of the formula, add that as well.