Hello,

I am trying to run a loop that automatically finds a cell value from Worksheet 1 on Worksheet 2 and then special pastes the cell's background color to the corresponding cell on Worksheet 1. For example, it sees cell "Apple" on Worksheet 1, finds a cell with "Apple" which happens to have a red background on Worksheet 2, returns to Worksheet 1 to special paste a red background to cell "Apple".

The formula I use does not work. Please let me know what I'm doing wrong

Formula: copy to clipboard 


Sub Macro3()

'

' Macro3 Macro

'



'

Dim x As Integer

Z = 1



Do



Sheets("Sheet1").Activate

Cells(6, "x").Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Sheet2").Select

    Cells.find(What:="x", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate

    ActiveCell.Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Sheet1").Select

    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

        SkipBlanks:=False, Transpose:=False



ActiveCell.Offset(1, 0).Select

Z = Z + 1

If IsEmpty(ActiveCell) Then

Exit Do

End If

Loop Until Z = 330

End Sub