Copy the formatting of one cell to other cell

[Subodh]

I have a cell in sheet1.
I want to copy the cell formatting to other cell in other sheet
Sheet2.
=Sheet1!A1 won't work as it will just copy the cells value but not
formatting.
How can i copy the formatting using the VBA Code?
Thanks in advance.

 

[Per Jessen]

Hi

Look at this:

Sheets("Sheet1").Range("A1").Copy
Sheets("Sheet2").Range("A2").PasteSpecial Paste:=xlFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Regards,
Per

 

[Dave Peterson]

Record a macro when you do a copy|paste special|formats and you'll have the
syntax.

 

[Rick Rothstein]

Did you want to copy the values also? If not, will there be other values in
the destination cells?

 

[Subodh]

Did you want to copy the values also? If not, will there be other values in
the destination cells?

--
Rick (MVP - Excel)






- Show quoted text -

The above code works to copy the formatting but not the values.
I want to copy the values also to the destination cell.
Also, I want the code to run whenever the change is made in the cell
A1 of Sheet1.

 

[Rick Rothstein]

You didn't say which "other cell" on Sheet2 you wanted to copy A1 from
Sheet1 to, so I guessed at C3 (change that as desired)...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "A1" Then
Target.Copy Worksheets("Sheet2").Range("C3")
End If
End Sub

This is event code and must go in Sheet1's code window. To do that,
right-click the tab at the bottom of Sheet1, select View Code from the popup
menu that appears and then copy/paste the above code into the code window
that appeared. Now, whenever you go back to Sheet1 and (manually, not
through a formula) change the value in A1, C3 on Sheet2 will have the same
value in it with the same formatting.

--
Rick (MVP - Excel)

Did you want to copy the values also? If not, will there be other values
in
the destination cells?

--
Rick (MVP - Excel)






- Show quoted text -

The above code works to copy the formatting but not the values.
I want to copy the values also to the destination cell.
Also, I want the code to run whenever the change is made in the cell
A1 of Sheet1.