Consulting

Results 1 to 3 of 3

Thread: NEED HELP IN GETTING MACRO TO WORK NON SPLIT DATA MACRO

  1. #1
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location

    NEED HELP IN GETTING MACRO TO WORK NON SPLIT DATA MACRO

    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

  2. #2
    VBAX Regular
    Joined
    Jan 2018
    Posts
    55
    Location
    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

  3. #3
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    EXACTLY!!!!! GREAT JOB!!! THANK YOU !!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •