Copying yellow cells in a range to another column on the same sheet with VBA

[paydog23]

I have a dataset that contains yellow cells (each yellow cell represents the final treatment plant or TP in a flow path) from Range("A2: F841"). Since the yellow cells are spread out randomly among the columns A to F, I would like to use VBA to copy them all as a single range in column J. I am doing this so that each well in column I has a corresponding final TP. Here's a screenshot of my spreadsheet

 

[paydog23]

Try this modification to Dante's code...

Sub Copying_yellow_cells()
  Dim i As Long, j As Long
  For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    For j = 1 To Columns("F").Column
      If Cells(i, j).DisplayFormat.Interior.Color = vbYellow Then
        Cells(i, "J").Value = Cells(i, j).Value
        Exit For
      End If
    Next
  Next
End Sub

Thanks, that worked!

 

[paydog23]

Try this (not tested)

Sub Copying_yellow_cells()
  Dim i As Long, j As Long
  For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    For j = 1 To Columns("F").Column
      If Cells(i, j).DisplayFormat.Interior.Color = vbYellow Then
        Cells(i, "J").Value = Cells(i, j).Value
        Exit For
      End If
    Next
  Next
End Sub

That worked, thanks!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.