estatefinds
08-30-2019, 07:34 AM
The code currently works with split data and having hard time trying to get to working with unsplit data.
in the worksheet there are four number combinations in column A starrting at row 20.
And in the columns K to R starting at row 10 are the same combinations.
so I am trying to accomplsh is this I select a combination in column A and the matching number found in columns K through R will highlight yellow.
for example:
I select the 1237 in column A row 20 and the event code will highlight in yellow or color yellow the 1237 in the column K row 16.
then when i move the cursor down to the next combination 1235 in column A it will highlight or color yellow the 1235 in column L row 10.
any help on this is appreciated!!!!
Thank you
Private Sub Worksheet_SelectionChange(ByVal Target As Range)Sub test()
Dim keyCell As Range
Dim SearchRange As Range
Dim writeCell As Range, oneCell
Dim Numerals As Variant, i As Long
If Selection.Column <> 1 Then Beep: Exit Sub
Set keyCell = Selection.Cells(1, 1)
Numerals = Split(CStr(keyCell.Value), "-")
With keyCell
Set SearchRange = Range(.Cells(2, 1), .EntireColumn.Cells(Rows.Count, 1).End(xlUp))
End With
SearchRange.Offset(0, 1).Resize(, 5).ClearContents
For i = 0 To UBound(Numerals)
Set writeCell = Nothing
For Each oneCell In SearchRange
If IsNumeric(Application.Match(Numerals(i), Split(oneCell.Value, "-"), 0)) Then
Set writeCell = oneCell
Exit For
End If
Next oneCell
If Not writeCell Is Nothing Then
With writeCell
.Offset(0, Application.Match(Numerals(i), Split(.Value, "-"), 0)).Value = writeCell.Row - keyCell.Row
End With
End If
Next i
End Sub
in the worksheet there are four number combinations in column A starrting at row 20.
And in the columns K to R starting at row 10 are the same combinations.
so I am trying to accomplsh is this I select a combination in column A and the matching number found in columns K through R will highlight yellow.
for example:
I select the 1237 in column A row 20 and the event code will highlight in yellow or color yellow the 1237 in the column K row 16.
then when i move the cursor down to the next combination 1235 in column A it will highlight or color yellow the 1235 in column L row 10.
any help on this is appreciated!!!!
Thank you
Private Sub Worksheet_SelectionChange(ByVal Target As Range)Sub test()
Dim keyCell As Range
Dim SearchRange As Range
Dim writeCell As Range, oneCell
Dim Numerals As Variant, i As Long
If Selection.Column <> 1 Then Beep: Exit Sub
Set keyCell = Selection.Cells(1, 1)
Numerals = Split(CStr(keyCell.Value), "-")
With keyCell
Set SearchRange = Range(.Cells(2, 1), .EntireColumn.Cells(Rows.Count, 1).End(xlUp))
End With
SearchRange.Offset(0, 1).Resize(, 5).ClearContents
For i = 0 To UBound(Numerals)
Set writeCell = Nothing
For Each oneCell In SearchRange
If IsNumeric(Application.Match(Numerals(i), Split(oneCell.Value, "-"), 0)) Then
Set writeCell = oneCell
Exit For
End If
Next oneCell
If Not writeCell Is Nothing Then
With writeCell
.Offset(0, Application.Match(Numerals(i), Split(.Value, "-"), 0)).Value = writeCell.Row - keyCell.Row
End With
End If
Next i
End Sub