CODEWITHSUNDEEP
excelvba
Martin
Martin

Reputation: 69

Find and paste formatting

I have two project datasets on different sheets, Sheet1 & Sheet2. Each has different details about a project, and so I've created Sheet3 to combine relevant details about each project into one place.

Sheet1 and Sheet2 should have the same formatting for each project, but as it turns out, that is not the case. Copying the formatting from Sheet1 is easy and is working, but as luck would have it, the more accurate formatting is on Sheet2.

I need help writing a Macro that first clears all formatting on a range in Sheet3, then gets the project name from a cell on Sheet3 (say C28), looks for the cell value in the Sheet2 range, copies that formatting and pasting it back into C28 on Sheet3 - not only C28 though, but B28 to F28. Then loops on to the next project till it reaches the end of the Sheet3 data range (row 536).

Sheet3 data range to clear formatting on: B28:F536
Sheet3 lookup data range: C28:C536
Sheet2 data range: C3:EG24 - where the Macro will find the cell formatting to copy.

Some cells in the Sheet3 lookup range will be blank, or won't have a match in the Sheet2 range - the macro should then just skip over and continue.

Any help would be appreciated!

Upvotes: 1

Views: 65

Answers (1)

Martin
Martin

Reputation: 69

This works:

  On Error Resume Next

 For Each cell In Intersect(Sheets("Sheet3").Range("C28:C536"), Sheets("Sheet3").UsedRange)
    
    With Worksheets("Sheet2").Range("A3:EG24")
        Set CellFound = .Find(cell.Value, LookIn:=xlValues)
        CellFound.Copy(cell).PasteSpecial Paste:=xlPasteFormats
       
    End With
    
 Next cell

It's part of a larger Macro that does the formatting and other checks. Didn't think it was going to be that simple!

Upvotes: 0

Related Questions