Match numbers and highlight

[James Niven]

Hello,

Please refer to my attachment.
I have worked out the last 10 rows of columns A to K, I beleive.

What I want to do next is copy the date from column E and starting at column M2 paste here of the last 10 rows.
Then, match the numbers in columns N to BO and just highlight the numbers only.

So, row 22 dated 07/24/2013 has 2, 6, 10, 38, 30 and 19, I want to highlight as per the attachment.

I hope this is clear.
I have started some VBA code but only a beginner.

Thanks

[SamT]

First thing is to get the last ten date cells

Dim LastTenDates As Range
Set LastTenDates = Range("E2").End(xlDown).Offset(-9, 0).Resize(10, 1)
How it works:

Range("E2").End(xlDown) sets it to the bottom cell
.Offset(-9, 0). sets it to the tenth cell from the bottom
.Resize(10. 1) Sets it to the itself (1) + the next 9 cells down.


Now we can paste them to M2

LastTenDates.Copy Range("M2")
We can shorten that to one line.

Range("E2").End(xlDown).Offset(-9, 0).Resize(10. 1).Copy Range("M2")
Which version we use depend on the algorithm we use for the rest of the code.



We can loop thru LastTenDates and loop thru their respective numbers and make ranges of the respective rows in the M2 number table, finding each number and coloring it.

OR

We can Loop thru the M2 date column, find the corresponding date in "E" and make a Numbers range to loop thru, then loop thru the M2 table rows, finding and coloring cells.

I think the first will give simpler, easier to understand,and code

And here is the final tested code
Sub LastTen()
Dim LastTenDates As Range
Dim rw As Long 'Number of Rows in M-table and E-dates
Dim Num As Long 'Number of cells in E-dates and their numbers
Dim MNums As Range
Dim ENums As Range

'Clear all colors
Range("N2").Resize(10, 54).Interior.ColorIndex = xlColorIndexNone

'Copy the dates over
Set LastTenDates = Range("E2").End(xlDown).Offset(-9, 0).Resize(10, 1)
LastTenDates.Copy Range("M2")

'Loop down the rows of both date ranges
For rw = 2 To 11
Set MNums = Range("N" & rw).Resize(1, 54)
Set ENums = LastTenDates.Cells(rw - 1)

'Loop across the nubers in Columns D-K, Finding them in the M-table
For Num = 1 To 6
MNums.Find(ENums.Offset(0, Num), , _
xlValues, xlWhole, xlByColumns, xlNext) _
.Interior.ColorIndex = 6
Next Num
Next rw
End Sub

[James Niven]

Thanks SamT,

This worked like a charm, exactly what I am wanting and the code is so easy to follow. I really love the way you broke the explanation down, it will help me out on trying to understand how you got there. I can see there are many different ways of doing things in VBA. I was able to find the last 10 rows myself, but just was not able to carry out the remainder!!

I ended up changing the highlight to a green, easier to see on the screen.

Thanks again, now I am able to move on with this project!!