Code needed to copy cell & column header depending on cell interior colour that is applied via conditional formatting

[Upex]

Hi All,

Thanks for taking the time to look at this for me and thanks for any replies.

I have a spreadsheet with around 30 columns that all contain different conditional formatting criteria (per column).

I'm after code to run on a button that will use the active row and copy all cells for that row that have the interior colour of red (generated via conditional formatting) and paste them into a different location. In addition, I'd like it to also copy across the column header for each red cell. Then do the same for amber entries also.

Speadsheet example:

<TABLE style="WIDTH: 259pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=345 x:str><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=89></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=64>Apps</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=64>Subs</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=64>Tests</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=64>etc</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 height=17>Paul</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: red; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl25 align=right x:num>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ff9900; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl26 align=right x:num>76</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ff9900; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl26 align=right x:num>11</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 height=17>John</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ff9900; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl26 align=right x:num>12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #99cc00; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>95</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: red; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl25 align=right x:num>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 height=17>Sam</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #99cc00; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>17</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: red; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl25 align=right x:num>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ff9900; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl26 align=right x:num>8</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 height=17>etc</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR></TBODY></TABLE>

Expected (wanted) results:

<TABLE style="WIDTH: 211pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=281 x:str><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 height=17 width=89>Actions for:</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=64>Paul</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: red; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl25 height=17>Red Alerts</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27>Apps</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ff9900; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl26 height=17>Amber Alerts</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27>Subs</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27>Tests</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>76</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>11</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR></TBODY></TABLE>

Any help would be fantastic and thanks to you all.

Cheers,

Upex

 

[brans1982]

Hi,

Where do you want the info copied to? A differen't sheet or on the same one?

Thanks,

Conor.

 

[Upex]

Hi Conor,

Thanks for looking at this for me.

I would idealy like the data entered onto a new excel document so can be saved / messed around with away from the original document. Otherwise, a seperate sheet would be great.

I haven't ever worked with code & conditional formatting before this - try to avoid it wherever possible, so thanks for your help.

Cheers,

Upex.

 

[brans1982]

Hi Upex,

Sorry I'm only getting back to you now. I haven't had a chance to look at this until now.

So.....

I need you to name the sheet with the unformatted results as "Data" and the sheet you want to code to paste the data as "Results".

In the worksheetm "Data", the names have to be in A1, A2, A3 ect ect. and the titles have to be in B1, C1, D1, E1 ect ect.

You can have as many names or titles as you need.

Then, copy this macro into your VBA Editor (Alt+F11)....

Sub Test()
Dim Res As Worksheet, _
Data As Worksheet

Set Res = Worksheets("Results")
Set Data = Worksheets("Data")

Application.ScreenUpdating = False

Data.Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row & "").Copy
Res.Range("A2").PasteSpecial xlValues

Res.Activate

FinalRow = Res.Cells(Rows.Count, 1).End(xlUp).Row

For i = FinalRow To 3 Step -1

For j = 1 To 3
Cells(i, 1).Insert Shift:=xlDown
Next j

Next i

With Range("B2")
.Offset(0, 0) = "Title"
.Offset(1, 0) = "Red"
.Offset(2, 0) = "Title"
.Offset(3, 0) = "Amber"
End With

Range("B2").Resize(4, 1).Copy

FinalRow = Res.Cells(Rows.Count, 1).End(xlUp).Row

For i = 6 To FinalRow Step 4

Cells(i, 2).PasteSpecial xlValues

Next i

Data.Activate

EndRow = Data.Cells(Rows.Count, 1).End(xlUp).Row
EndCol = Data.Cells(1, Columns.Count).End(xlToLeft).Column

StartRow = 2

For i = 2 To EndRow
For j = 2 To EndCol

If i > 2 And j = 2 Then

StartRow = StartRow + 4

End If

Select Case Cells(i, j).Interior.ColorIndex

Case 3

Cells(1, j).Copy
Res.Cells(StartRow, Res.Cells(StartRow, Columns.Count).End(xlToLeft).Column + 1).PasteSpecial xlValues
Cells(i, j).Copy
Res.Cells(StartRow + 1, Res.Cells(StartRow + 1, Columns.Count).End(xlToLeft).Column + 1).PasteSpecial xlValues

Case 45

Cells(1, j).Copy
Res.Cells(StartRow + 2, Res.Cells(StartRow + 2, Columns.Count).End(xlToLeft).Column + 1).PasteSpecial xlValues
Cells(i, j).Copy
Res.Cells(StartRow + 3, Res.Cells(StartRow + 3, Columns.Count).End(xlToLeft).Column + 1).PasteSpecial xlValues

End Select

Next j
Next i

Res.Activate

FinalRow = Res.Cells(Rows.Count, 1).End(xlUp).Row

Range("A2:A5").Merge

With Range("A2:B5")
.Borders.ColorIndex = 15
.Borders.Weight = xlMedium
End With

Range("B2:B5").Borders(xlInsideHorizontal).Weight = xlThin

Range("A2:B5").Copy

For i = 6 To FinalRow Step 4

Cells(i, 1).PasteSpecial xlFormats

Next i

For i = 2 To FinalRow + 4

Select Case Cells(i, 2)

Case "Red"

Cells(i, 2).Interior.ColorIndex = 3

Case "Amber"

Cells(i, 2).Interior.ColorIndex = 45

End Select

Next i

For i = 2 To FinalRow Step 8
Cells(i, 1).Interior.ColorIndex = 35
Next i

Application.CutCopyMode = False

Range("A1").Select

End Sub

My VBA isn't very good so I'm sure there is a better way to do this but it gets the job done!

Any problems, let me know.

Conor.

 

[Upex]

Brans1982

Many many thanks for your code. Definately more involving than I thought it would be. I will have a look at, try to understand and have a test of it when I get chance and of course will reply back with how I get on.

Many thanks for your time and assistance. Here's hoping it does the job.

Cheers, Upex.

 

[Upex]

Brans1982.

Have just managed to grab 5 misn to try your code.

Works well except that it isn't bringing over the data that is flagged up as red or amber. E.g this is what i tried:

Data -
<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=64></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>Title1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>Title 10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>Title 100</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>person 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: #ff9900; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: style; mso-pattern: auto none" align=right x:num>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: #ff9900; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: style; mso-pattern: auto none" align=right x:num>45</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: #ff9900; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: style; mso-pattern: auto none" align=right x:num>659</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>person 2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: #ff9900; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: style; mso-pattern: auto none" align=right x:num>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: lime; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: style; mso-pattern: auto none" align=right x:num>68</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: red; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: style; mso-pattern: auto none" align=right x:num>365</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>person 3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: #339966; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: style; mso-pattern: auto none" align=right x:num>9</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: red; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: style; mso-pattern: auto none" align=right x:num>24</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND: lime; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: style; mso-pattern: auto none" align=right x:num>758</TD></TR></TBODY></TABLE>

Results -
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver 1pt solid; BACKGROUND-COLOR: #ccffcc; WIDTH: 48pt; HEIGHT: 54pt; BORDER-TOP: silver 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl29 height=72 rowSpan=4 width=64>person 1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl25 width=64>Title</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: red; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl27 height=18>Red</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl26 height=18>Title</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: #ff9900; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl28 height=18>Amber</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl24 height=72 rowSpan=4>person 2</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl25>Title</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: red; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl27 height=18>Red</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl26 height=18>Title</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: #ff9900; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl28 height=18>Amber</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver 1pt solid; BACKGROUND-COLOR: #ccffcc; HEIGHT: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl29 height=72 rowSpan=4>person 3</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl25>Title</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: red; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl27 height=18>Red</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl26 height=18>Title</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: #ff9900; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl28 height=18>Amber</TD></TR></TBODY></TABLE>


What I'm after is it to collate the amber and red cells and report them back (i.e. tell me what title and what values are assosiated to the red and amber values, per person).

Expected result:
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver 1pt solid; BACKGROUND-COLOR: #ccffcc; WIDTH: 48pt; HEIGHT: 78pt; BORDER-TOP: silver 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl30 height=104 rowSpan=6 width=64>person 1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl25 width=64>Title 1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: #ff9900; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl29 height=17 align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl26 height=17>Title 10</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: #ff9900; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl28 height=18 align=right x:num>45</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl25 height=17>Title 100</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: #ff9900; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl29 height=18 align=right x:num>659</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 54pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl24 height=72 rowSpan=4>person 2</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl25>Title 1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: #ff9900; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl29 height=18 align=right x:num>6</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl26 height=18>Title 100</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: red; HEIGHT: 13.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl33 height=18 align=right x:num>365</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: silver 1pt solid; BACKGROUND-COLOR: #ccffcc; HEIGHT: 25.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl30 height=34 rowSpan=2>person 3</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl25>Title 10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: red; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl27 height=17 align=right x:num>24</TD></TR></TBODY></TABLE>

I copied your code exactly - nothing is different to what you posted here.

Many thanks if you get chance to look again at this.

Regards, Rob.