Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code macro for duplicate entries
Hello friends,
I have my data in the range J14:AA14. Please help me to create a VBA macro which will find duplicate entries in this range and colour both cells red. Thanx -- KCG |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code macro for duplicate entries
Select your range and run:
Sub DupFinder() Dim r As Range, t As Range Set t = Selection For Each r In t v = r.Value If Application.WorksheetFunction.CountIf(t, v) 1 Then r.Interior.ColorIndex = 3 End If Next End Sub This is a general routine based on Selection. If you don't want to have to Select then replace: Set t = Selection with: Set t = Range("J14:AA14") -- Gary''s Student - gsnu200781 "KCG" wrote: Hello friends, I have my data in the range J14:AA14. Please help me to create a VBA macro which will find duplicate entries in this range and colour both cells red. Thanx -- KCG |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code macro for duplicate entries
Hello Gary's Student,
I tried your suggestion and it worked fine. I have now changed the range to J14:AA16. I also have the following macro which I would like to merge with your suggestion. (This macro 'kicks in' every 16 rows automatically when I run it): Public Sub ProcessData(StartRow) For i = 10 To 14 For j = (StartRow + 1) To (StartRow + 1) For k = 10 To 27 For L = (StartRow + 2) To (StartRow + 15) If Cells(j, i).Value = Cells(L, k).Value Then Cells(L, k).Font.ColorIndex = 38 End If Next Next Next Next End Sub -------------------------------------------------------------------------------------- Public Sub ProcessFile() sectionRows = 16 startRows = 3 startCheckRow = 4 canContinue = (Cells(startCheckRow, 2).Value 0) While canContinue ProcessData (startRows) startRows = startRows + sectionRows startCheckRow = startCheckRow + sectionRows canContinue = (Cells(startCheckRow, 2).Value 0) Wend End Sub How do I merge the two macros? Thanx for your help. -- KCG "Gary''s Student" wrote: Select your range and run: Sub DupFinder() Dim r As Range, t As Range Set t = Selection For Each r In t v = r.Value If Application.WorksheetFunction.CountIf(t, v) 1 Then r.Interior.ColorIndex = 3 End If Next End Sub This is a general routine based on Selection. If you don't want to have to Select then replace: Set t = Selection with: Set t = Range("J14:AA14") -- Gary''s Student - gsnu200781 "KCG" wrote: Hello friends, I have my data in the range J14:AA14. Please help me to create a VBA macro which will find duplicate entries in this range and colour both cells red. Thanx -- KCG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate entries | Excel Worksheet Functions | |||
Macro for checking duplicate entries | Excel Worksheet Functions | |||
Getting rid of not quite duplicate entries | Excel Worksheet Functions | |||
Duplicate entries | New Users to Excel | |||
Duplicate entries | Excel Worksheet Functions |