Highlight duplicate cells in non-sequential, non-stationary cells that only contain 7 or 8 digit numbers

[larz1620]

Hi. I am new to posting so hopefully I do this right. I can't figure out how to attach my file so I pasted part of it below the code. I changed the actual text (I'm not working on animals)

Background:
I have a template my team uses but the conditional formatting does not work well for our diverse requirements. We need to identify duplicate ID numbers within our worksheet (by coloring in the cell). We are constantly updating our worksheet by over writing cell contents, copy/paste/insert/del rows and columns, etc.

Requirements to highlight duplicate cells:
1. don't count as duplicate if cell contains "-" or "na" (which would not be needed if I could get the VBA to recognize only cells with 7 or 8 digit numbers).
2. Only find duplicates if cells contain 7 or 8 digit numbers.

Right now I am using conditional formatting and setting ranges. However, I have to keep resetting the ranges for my team in each of their worksheets every time they add/move/del rows/columns. Other team members just ignore the highlighting which defeats the purpose and causes poor work.

So... to my question. I tried for a few months to figure out a way using VBA to meet the requirements. Currently I have the code below which gives me a run time error #1004 and stops at the command "Set rRange..."

Duplicate cells with 7-8 digit numbers can be contained in various places through out the rows and columns generally grouped like $J$2:$U$5,$J$8:$U$9,$D$10:$G$24 but rows & columns may get added before/after and within these areas. The grouping also crosses over each other causing non-duplicates to highlight when in the cross-section.

I am open to other suggestions too.

This is what I have in VBA...
...

Dim rRange As Range​

Dim rCell As Range​

​

'I want to identify all DUPLICATE cells in this worksheet ONLY IF the​

' cells contain either 7 or 8 digit numbers...​

​

Set rRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlNumbers)​

​

rRange.Select.rCell​

Selection.FormatConditions.AddUniqueValues​

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority​

Selection.FormatConditions(1).DupeUnique = xlDuplicate​

With Selection.FormatConditions(1).Font​

.Color = -16383844​

.TintAndShade = 0​

End With​

With Selection.FormatConditions(1).Interior​

.PatternColorIndex = xlAutomatic​

.Color = 13551615​

.TintAndShade = 0​

End With​

Selection.FormatConditions(1).StopIfTrue = False​

'If my request is not possible, how can I name or otherwise identify cells that will not​

' require modifing the macro/conditional formatting if row and or columns are copied​

' and pasted (keep the cell type for copied rows/columns)​

​

...

​

<tbody> </tbody>
									Color1	12340015	12340016	12340020	12340018
									Color2	12340027	12340028	12340029	12340030
									Color3	-	-	-	-
									Color4	-	-	12340020	-
									PropertyA	Free	Free	Free	Free
									PropertyB	Farm	Farm	Farm	Farm
									Data ID	2340051	8340052	5126474	2340151
Name	Location1	Location2	Base ID	Color1	Color2	Color3	Color4		Base ID	12340039	12340040	12340041	12340042
Fish	MI	48001	12340000	12340053	-	-	-		note	-	55	-	-
Fish	OH	43728	12340001	-	12340054	-	-		note	12	-	-	-
Fish	TX	79031	12340002	-	-	12340055	-		note	g:1, w:2	6	-	-
Bird	MI	48059	12340003	-	12340057	-	12340056		note	2	2	2	2
Bird	OH	45732	12340004	12340020	9876432	-	-		note	-	-	3	-
Bird	TX	88132	12340005	na	na	na	na		note	-	5	9	-
Bird	TX	88130	12340006	12343436	-	-	-		note	1	1	1	1
Cat	MI	48091	12340007	5330006	-	-	12340032		note	-	1	-	-
Cat	MI	48023	12340008	32140006	12340057	-	12340028		note	-	-	4	-
Cat	OH	43728	12340009	23140006	-	-	12340053		note	-	1	-	-
Cat	TX	79312	12340010	22340006	-	-	-		note	-	-	-	-

<tbody>
</tbody>

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

I think this works as a conditional format:

=AND(A1>1000000,A1<99999999,COUNTIF($A$1:$N$20,"=" & A1)>1)

I pasted in your data and A1 was the first cell and $A$1:$N$20 was the last cell. That may need to be changed depending on your data range.