PDA

View Full Version : [SOLVED] NEED HELP IN GETTING MACRO TO WORK NON SPLIT DATA MACRO



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

yujin
08-31-2019, 11:44 PM
Like this?



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Count > 1 Then Exit Sub

Dim SearchRange As Range
Dim DataRange As Range
Dim FoundCell As Range


Set SearchRange = Range("A:A")
Set DataRange = Range("K:R")
SearchRange.ClearFormats
DataRange.ClearFormats

Target.Interior.Color = XlRgbColor.rgbYellow
Set FoundCell = DataRange.Find(What:=Target.Value, LookAt:=xlWhole)
If Not FoundCell Is Nothing Then
FoundCell.Interior.Color = XlRgbColor.rgbYellow
End If
End Sub

estatefinds
09-02-2019, 08:13 AM
EXACTLY!!!!! GREAT JOB!!! THANK YOU !!!!!:clap::clap::clap::clap::clap::clap::clap::clap: