VBA, scroll through column looking for yellow cells

[dappy]

Hi,

I've searched but had no luck. Hopefully this is easy for someone I need to scroll down a column an if a cell is yellow then copy that content to another column. It will always be the same column for the yellow cells and the target column would be next to it.
There are some rows in the sheet that are empty though. Is this doable?

Help much appreciated and so much thanks in advance!

 

[Joe4]

How are these cells being colored yellow?
Is it being done manually, or via Conditional Formatting?
What are the columns involved?

 

[dappy]

Manually. Does this affect the code then?

 

[Joe4]

Try this code.

You will need to change the column letter to match whatever column you want to check (you did not answer that question for me):

Sub MyCopyYellowCells()
    
    Dim c As String
    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False

'   Designate which column to check
    c = "P"
    
'   Find last row in column with data
    lr = Cells(Rows.Count, c).End(xlUp).Row
    
'   Loop through all rows
    For r = 1 To lr
'       Check color of cell to see if it is yellow
        If Cells(r, c).Interior.Color = 65535 Then
'           Populate column to right
            Cells(r, c).Offset(0, 1).Value = Cells(r, c).Value
        End If
    Next r
        
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[dappy]

That is perfect, thank you so much, you're the best! you've save me

 

[Joe4]

You are welcome.
Glad I was able to help!