title: Return Row 1 of Column where match was found
body: <p>On my workbook, a command button is looping through every cell in column A, D, G and J.<br />
  If the cell contains a blue border, it's searching for its match on another workbook.<br />
  If that match is found, it's placing the cell value from original workbook to the 2nd workbook, in the next column of where the match was found.</p>
  <p>I have 2 if statements checking if the next column is empty, and if it is then put the value there, if not then find the next empty cell in that row and put there.</p>
  <p>I'm trying to return the first row (A1, D1, G1, or J1) from the original workbook, into the adjacent column of the newly placed values on the 2nd workbook.</p>
  <p>Example:</p>
  <ul>
  <li>On Workbook 1, the name &quot;John Doe&quot; and &quot;Jane Doe&quot; have a blue border in Column A.</li>
  <li>On Workbook 2, &quot;John Doe&quot; was found in Column A, Row 123 and &quot;Jane Doe&quot; was found in Column A, Row 250.</li>
  <li>The macro places &quot;John Doe&quot; in Column B, Row 123 and &quot;Jane Doe&quot; in Column B, Row 250 (assuming that cell in B123 and B250 are empty).</li>
  </ul>
  <p>From Workbook 1, I want to also place the cell value in A1 - Into Workbook 2: Column C, Row 123 and 250.</p>
  <p>But I want to do this for Columns A D G J simultaneously (rr3dest in my below code is what I'm trying to set this value to, I know it's not set to anything right now).</p>
  <pre class="lang-vb prettyprint-override"><code>Private Sub CommandButton3_Click()
  
  Dim testWS As Worksheet
  Dim testRange As Range, idCella As Range
  Dim alastRow2 As Long, resultM As Integer
  Dim rr2dest As Range, rr3dest As Range
  
  Set testWS = Workbooks(&quot;Test.xlsx&quot;).Worksheets(&quot;October&quot;)                                       'set the 2nd workbook as testWS
  Set testRange = testWS.Columns(1)                                                                                            'searching only column A on testWS (2nd workbook)
  alastRow2 = Worksheets(&quot;Reruns To Pull&quot;).Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row                   'find last row in column A that has data on current workbook
  dlastRow2 = Worksheets(&quot;Reruns To Pull&quot;).Cells(Rows.Count, &quot;D&quot;).End(xlUp).Row
  glastrow2 = Worksheets(&quot;Reruns To Pull&quot;).Cells(Rows.Count, &quot;G&quot;).End(xlUp).Row
  jlastrow2 = Worksheets(&quot;Reruns To Pull&quot;).Cells(Rows.Count, &quot;J&quot;).End(xlUp).Row
  
  
  For Each idCella In Worksheets(&quot;Reruns To Pull&quot;).Range(&quot;A1:A&quot; &amp; alastRow2 &amp; &quot;,D1:D&quot; &amp; dlastRow2 &amp; &quot;,G1:G&quot; &amp; glastrow2 &amp; &quot;,J1:J&quot; &amp; jlastrow2).Cells                'for each cell in Column A on current workbook (eventually I want to loop through Column A, D, G, J.  All will be variable ranges)
  
          If idCella.Borders.Color = RGB(0, 0, 192) Then                                                 'On current workbook, if cells in Col A borders.color = blue then
  
              If Not IsError(Application.Match(idCella.Value, testRange, 0)) Then                                      'find exact match on Test.xlsx (2nd workbook) and store in variable resultM
                  resultM = (Application.Match(idCella.Value, testRange, 0))
                                                                                         
              If IsEmpty(testWS.Range(&quot;A&quot; &amp; CStr(resultM)).Offset(0, 1)) Then                         ' if resultM.offset(0,1) is empty then set destination to .offset(0,1)
                  Set rr2dest = testWS.Range(&quot;A&quot; &amp; CStr(resultM)).Offset(0, 1)
                      rr2dest.Value = idCella.Value
                      rr2dest.Interior.Color = idCella.Interior.Color
                      rr2dest.Borders.Color = idCella.Borders.Color
                      rr2dest.Borders.Weight = idCella.Borders.Weight
                  Set rr3dest = testWS.Range(&quot;A&quot; &amp; CStr(resultM)).Offset(0, 2)
                      
              ElseIf Not IsEmpty(testWS.Range(&quot;A&quot; &amp; CStr(resultM)).Offset(0, 1)) Then                 ' if resultM.offset(0,1) is not empty then set destination to .end(xltoright).offset(0,1)
                  Set rr2dest = testWS.Range(&quot;A&quot; &amp; CStr(resultM)).End(xlToRight).Offset(0, 1)
                      rr2dest.Value = idCella.Value
                      rr2dest.Interior.Color = idCella.Interior.Color
                      rr2dest.Borders.Color = idCella.Borders.Color
                      rr2dest.Borders.Weight = idCella.Borders.Weight
                  End If
              End If
          End If
      
  Next idCella
  
      testWS.Range(&quot;A2:M80&quot;).WrapText = True
      testWS.Columns(&quot;A:M&quot;).HorizontalAlignment = xlCenter
      testWS.Columns(&quot;A:M&quot;).VerticalAlignment = xlVAlignCenter
      
  End Sub 
  </code></pre>