return row 1 of the column where a match was found

Compiled but not tested:

Private Sub CommandButton3_Click()

    Dim testWS As Worksheet, pullWS As Worksheet
    Dim testRange As Range, idCella As Range
    Dim arrSourceCols, col, v, m, c As Range
    Set testWS = Workbooks("Test.xlsx").Worksheets("October")   'set the 2nd workbook as testWS
    Set testRange = testWS.Columns(1)                           'searching only column A on testWS (2nd workbook)
    Set pullWS = ThisWorkbook.Worksheets("Reruns To Pull")
    arrSourceCols = Array("A", "D", "G", "J") 'columns to be scanned and matched
    For Each col In arrSourceCols   'loop source columns
        For Each idCella In pullWS.Range(pullWS.Cells(1, col), _
                                         pullWS.Cells(Rows.Count, col).End(xlUp)).Cells
            If idCella.Borders.Color = RGB(0, 0, 192) Then
                v = idCella.Value                      'value to look for
                m = Application.Match(v, testRange, 0) 'match?
                If Not IsError(m) Then
                    Set c = testWS.Cells(m, Columns.Count).End(xlToLeft).Offset(0, 1) 'get empty cell
                    c.Value = v                                       'put the matched value
                    CopyFormats idCella, c                            'transfer formatting
                    c.Offset(0, 1).Value = pullWS.Cells(1, col).Value 'put the header from the column
                End If  'matched
            End If      'blue borders
        Next idCella
    Next col

    testWS.Range("A2:M80").WrapText = True
    testWS.Columns("A:M").HorizontalAlignment = xlCenter
    testWS.Columns("A:M").VerticalAlignment = xlVAlignCenter
End Sub

Sub CopyFormats(cFrom As Range, cTo As Range)
    With cTo
        .Interior.Color = cFrom.Interior.Color
        .Borders.Color = cFrom.Borders.Color
        .Borders.Weight = cFrom.Borders.Weight
    End With
End Sub

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top