VBA code to copy/paste the formatting of the entire workbook.

[souravgangawat]

Hello Reader,

I am trying to write a VBA code, which copy/pastes the formatting (just the cell background) of the used range of one workbook to the another. It should just copy/paste the cell background and nothing else formats say, fonts, cell styles etc.

I have tried my best, but not yet reached the solution. If you have done something like this in the past, your help is much appreciated !

PS: Both the worksheets from where the cell background is copied and where it is been pasted has the same sheet names and the same sheet structure.

Thanks,
Sourav

[p45cal]

Perhaps something along these lines (untested):

Sub blah()
Set SceWkBk = ThisWorkbook  'or whatever the source workbook is.
Set DestWkBk = Workbooks("something.xls")  'or whatever the estination workbook is.

For Each SceSheet In SceWkBk.Sheets
  Set DestSht = DestWkBk.Sheets(SceSht.Name)
  For Each cll In SceSht.UsedRange.Cells
    DestSht.Range(cll.Address).Interior.ColorIndex = cll.Interior.ColorIndex
  Next cll
Next SceSht
End Sub

I'm using Excel 2003 here right now and it might be you want to copy more/different cell properties across. Record a macro of your making some cell format changes of the ilk that you want to copy across, look at the code, and you can usually delete 90% of the properties therein as they're the default values anyway. If you're not sure, post a copy of the recorded macro here and say what aspects of the cell formatting you want to transfer.