VBA Duplicates in a group based on 2 columns

[Stephen_IV]

Good afternoon,

I have data based on 2 columns column B and E. What I would like to do is to highlight the group in yellow if the name and the DOB match. For instance AAAA and DDDD would be highlighted in this example.

Thanks in advance!!!!!

<style type="text/css">
	table.tableizer-table {
		font-size: 12px;
		border: 1px solid #CCC ; 
		font-family: Arial, Helvetica, sans-serif;
	} 
	.tableizer-table td {
		padding: 4px;
		margin: 3px;
		border: 1px solid #CCC ;
	}
	.tableizer-table th {
		background-color: #104E8B ; 
		color: #FFF ;
		font-weight: bold;
	}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th></th><th>NAME</th><th> </th><th> </th><th>DOB</th></tr></thead><tbody>
 <tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
 <tr><td> </td><td>AAAA</td><td> </td><td> </td><td>7/6/2011</td></tr>
 <tr><td> </td><td>AAAA</td><td> </td><td> </td><td>7/6/2011</td></tr>
 <tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
 <tr><td> </td><td>BBBB</td><td> </td><td> </td><td>10/26/1987</td></tr>
 <tr><td> </td><td>BBBB</td><td> </td><td> </td><td>10/18/1991</td></tr>
 <tr><td> </td><td>BBBB</td><td> </td><td> </td><td>10/22/2000</td></tr>
 <tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
 <tr><td> </td><td>CCCC</td><td> </td><td> </td><td>10/3/2002</td></tr>
 <tr><td> </td><td>CCCC</td><td> </td><td> </td><td>8/15/2006</td></tr>
 <tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
 <tr><td> </td><td>DDDD</td><td> </td><td> </td><td>8/7/2002</td></tr>
 <tr><td> </td><td>DDDD</td><td> </td><td> </td><td>8/7/2002</td></tr>
 <tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
 <tr><td> </td><td>EEEE</td><td> </td><td> </td><td>6/27/1994</td></tr>
 <tr><td> </td><td>EEEE</td><td> </td><td> </td><td>4/15/1995</td></tr>
 <tr><td> </td><td>EEEE</td><td> </td><td> </td><td>12/31/1989</td></tr>
</tbody></table>

 

[oldbrewer]

name

dob

aaaa

01/08/1994

x

aaaa

01/08/1994

x

bbbb

02/08/1993

bbbb

03/07/1994

cccc

05/08/1990

x

cccc

05/08/1990

x

cccc

05/08/1990

x

cccc

05/08/1990

x

cccc

05/08/1990

x

rows marked x have been coloured by this

conditional formatting formula

cell A4 (the first aaaa)

=OR(AND(A4=A5,B4=B5),AND(A5="",B5="",A4=A3,B4=B3),AND(A3="",B3="",A4=A5,B4=B5))=TRUE

the bbbb's have not because the date is different

<colgroup><col><col><col span="11"></colgroup><tbody>
</tbody>

 

[Stephen_IV]

Hi oldbrewer for replying but I am looking for a VBA approach. If anyone can help. Thanks!

 

[JoeMo]

This assumes your headers are in row 1 and first group name is in B3.

Sub Steohen_IV()
Dim Ar As Range
Application.ScreenUpdating = False
For Each Ar In Range("B3:E" & Cells(Rows.Count, "E").End(xlUp).Row).SpecialCells(xlCellTypeConstants).Areas
    If Application.CountIf(Ar.Columns(1), Ar.Cells(1, 1)) = Ar.Rows.Count And Application.CountIf(Ar.Columns(4), Ar.Cells(1, 4)) = Ar.Rows.Count Then
        Ar.Interior.Color = vbYellow
    End If
Next Ar
Application.ScreenUpdating = True
End Sub

 

[Stephen_IV]

Thanks JoeMo,

That seems to do the trick. Much appreciated.

 

[JoeMo]

You are welcome - thanks for the reply.