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

L

larz1620

New Member
Joined
Oct 8, 2013
Messages
1
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. :rolleyes: I changed the actual text (I'm not working on animals) :laugh:

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)​

​
...

​

[TABLE="width: 864"]
<tbody>[TR]
[TD="align: left"]
clip_image001.png

<tbody>
</tbody>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Color1[/TD]
[TD]12340015[/TD]
[TD]12340016[/TD]
[TD]12340020[/TD]
[TD]12340018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Color2[/TD]
[TD]12340027[/TD]
[TD]12340028[/TD]
[TD]12340029[/TD]
[TD]12340030[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Color3[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Color4[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]12340020[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PropertyA[/TD]
[TD]Free[/TD]
[TD]Free[/TD]
[TD]Free[/TD]
[TD]Free[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PropertyB[/TD]
[TD]Farm[/TD]
[TD]Farm[/TD]
[TD]Farm[/TD]
[TD]Farm[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Data ID[/TD]
[TD]2340051[/TD]
[TD]8340052[/TD]
[TD]5126474[/TD]
[TD]2340151[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Location1[/TD]
[TD]Location2[/TD]
[TD]Base ID[/TD]
[TD]Color1[/TD]
[TD]Color2[/TD]
[TD]Color3[/TD]
[TD]Color4[/TD]
[TD][/TD]
[TD]Base ID[/TD]
[TD]12340039[/TD]
[TD]12340040[/TD]
[TD]12340041[/TD]
[TD]12340042[/TD]
[/TR]
[TR]
[TD]Fish[/TD]
[TD]MI[/TD]
[TD]48001[/TD]
[TD]12340000[/TD]
[TD]12340053[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD][/TD]
[TD]note[/TD]
[TD]-[/TD]
[TD]55[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Fish[/TD]
[TD]OH[/TD]
[TD]43728[/TD]
[TD]12340001[/TD]
[TD]-[/TD]
[TD]12340054[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD][/TD]
[TD]note[/TD]
[TD]12[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Fish[/TD]
[TD]TX[/TD]
[TD]79031[/TD]
[TD]12340002[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]12340055[/TD]
[TD]-[/TD]
[TD][/TD]
[TD]note[/TD]
[TD]g:1, w:2[/TD]
[TD]6[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Bird[/TD]
[TD]MI[/TD]
[TD]48059[/TD]
[TD]12340003[/TD]
[TD]-[/TD]
[TD]12340057[/TD]
[TD]-[/TD]
[TD]12340056[/TD]
[TD][/TD]
[TD]note[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Bird[/TD]
[TD]OH[/TD]
[TD]45732[/TD]
[TD]12340004[/TD]
[TD]12340020[/TD]
[TD]9876432[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD][/TD]
[TD]note[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]3[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Bird[/TD]
[TD]TX[/TD]
[TD]88132[/TD]
[TD]12340005[/TD]
[TD]na[/TD]
[TD]na[/TD]
[TD]na[/TD]
[TD]na[/TD]
[TD][/TD]
[TD]note[/TD]
[TD]-[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Bird[/TD]
[TD]TX[/TD]
[TD]88130[/TD]
[TD]12340006[/TD]
[TD]12343436[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD][/TD]
[TD]note[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]MI[/TD]
[TD]48091[/TD]
[TD]12340007[/TD]
[TD]5330006[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]12340032[/TD]
[TD][/TD]
[TD]note[/TD]
[TD]-[/TD]
[TD]1[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]MI[/TD]
[TD]48023[/TD]
[TD]12340008[/TD]
[TD]32140006[/TD]
[TD]12340057[/TD]
[TD]-[/TD]
[TD]12340028[/TD]
[TD][/TD]
[TD]note[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]4[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]OH[/TD]
[TD]43728[/TD]
[TD]12340009[/TD]
[TD]23140006[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]12340053[/TD]
[TD][/TD]
[TD]note[/TD]
[TD]-[/TD]
[TD]1[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]TX[/TD]
[TD]79312[/TD]
[TD]12340010[/TD]
[TD]22340006[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD][/TD]
[TD]note[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Duplicate cells not formatting problem.
Replies
6
Views
447
etaf
etaf
S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D
S
  • Question Question
Copy only those rows that have green highlighted cells on random columns
Replies
0
Views
519
Sunnyji
S
IIII
  • Solved
VBA: Highlight row within range if a date is a weekend or based on cell value
Replies
6
Views
2K
kokano90
K
H
  • Solved
Conditional formatting greatest value in each row of multiple columns, that ignores text/empty cells/0 values
Replies
7
Views
635
Joe4
Joe4

Forum statistics

Threads
1,226,848
Messages
6,193,315
Members
453,790
Latest member
yassinosnoo1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top