Need help restructuring or recreating VBA code Please!

[estatefinds]

I have combinations in column A.
I need a macro in which upon selecting a combination with the mouse or arrows keys the macro would select each of the five numbers found in the columns H:L, but only high light in yellow so i can still see the numbers; the ones that are uncolored. the ones colored in red will be ignored.
Also since I will be using large data in column A meaning from row 5 on, I need to be able to have the data I place how ever long that would be follow every 20 combinations in order to keep in view as I select the combinations, for example when I get down to row 20 the data in H:L will remain above, but instead I need it to follow as I select combinations. the data in H:L will change but the concept remains the same.
I am attaching file as example.

if any questions please ask.



Thank you in advance for your help!

[mdmackillop]

Part 1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Or Target.Cells.Count > 1 Then Exit Sub
On Error GoTo Exits
Application.EnableEvents = False
Call Test2(Target)
Exits:
Application.EnableEvents = True
End Sub

Sub Test2(Target As Range)
Dim r As Range, arr, a
Set r = Range("H:L").SpecialCells(2)
For Each cel In r
If cel.Interior.ColorIndex = 6 Then
cel.Interior.ColorIndex = xlNone
End If
Next
arr = Split(Target, "-")
For Each a In arr
Call DoFind(r, a)
Next
End Sub

Sub DoFind(r, v)
With r
Set c = .Find(v, Lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Interior.ColorIndex = xlNone Then c.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

[estatefinds]

hello,
How do I get this to run?

[Aussiebear]

Estatefinds.... do you understand anything about the code thats been provided by MD? What does the first 6 lines tell you?

[estatefinds]

yes that this is an event code, I had worked with this in the past but it has been a while on how to set up in the macros developer. I remember i had trouble with this before

[mdmackillop]

See here (http://www.excel-pratique.com/en/vba/worksheet_events.php)

[estatefinds]

ok i got it to work, I put in worksheet and change to selection change.

Thank you!

[estatefinds]

Thank you very much!!!
That is excatly what I was looking for Great Job!!!!!!

[estatefinds]

is there any way to get the data in H:L to follow down as I select data that is further down the worksheet?

[estatefinds]

or is there a way a scroll box could be placed so as i scroll throught the data in column A the H:L is still seen as i scroll through column A.

Thank you in advance!!!

[snb]

Please reduce your use of exclamation marks in threadtitles !!! ;)

[estatefinds]

no problem:)

[mdmackillop]

Start your column A data in Row 27 and freeze panes at Row 26

[estatefinds]

freeze panes at row 26? can you instruct me on this?

so i go to view then freeze panes.
do i select the data ar row 26 to freeze?

ok I selected the row 26 and click freeze pane and i ran macro and it worked!

Thanks again!

[mdmackillop]

Press the F1 key and type "Freeze Panes"

[estatefinds]

oh also if the data is larger in the H:L do i need to change the freeze pane location?

[mdmackillop]

What do you think?

[Paul_Hossler]

oh also if the data is larger in the H:L do i need to change the freeze pane location?

Should be easy enough to do a little experiment and see

Good way to learn also

[estatefinds]

so i chose a lower row like 30? just want to be sure it still works thank you!
Thanks again!!!

[estatefinds]

I just added over 400 or combinations now I am getting the X 400 error can you help?
please

[mdmackillop]

I'm afraid my psychic abilities are at a low ebb just now.

[Paul_Hossler]

I just added over 400 or combinations now I am getting the X 400 error can you help?
please

Not without new batteries for my telepathy helmet, or at least a sample file

[estatefinds]

Just corrected it I had to close and relaunch, Thank you again!

[mdmackillop]

Error handling added to initial post.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Or Target.Cells.Count > 1 Then Exit Sub
On Error GoTo Exits
Application.EnableEvents = False
Call Test2(Target)
Exits:
Application.EnableEvents = True
End Sub