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)


"Subodh" wrote in message
...
On Oct 14, 7:36 am, "Rick Rothstein"
wrote:
Did you want to copy the values also? If not, will there be other values
in
the destination cells?

--
Rick (MVP - Excel)

"Subodh" wrote in message

...



I have acellin sheet1.
I want to copy thecellformattingto othercellin other sheet
Sheet2.
=Sheet1!A1 won't work as it will just copy the cells value but not
formatting.
How can i copy theformattingusing the VBA Code?
Thanks in advance.- Hide quoted text -

- 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.