How do I copy a cell (content AND format) from one worksheet to a.

[Guest]

What worksheet function will copy both the content AND format of a cell in
one worksheet to a cell in another worksheet? I want to copy both text or
data and the format such as background color or border, under program
control. This would be a combination of both "=" function and "Format
Painter".

 

[Jason Morin]

You have to use VBA. What you could do is use a
worksheet change event so when you change the value in
for example, B3 on Sheet1, it copies the value and format
(change the formats first in B3 before the value), to
cell B10 on Sheet2. Something like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim FromWS As Worksheet
Dim ToWS As Worksheet
Set FromWS = Sheets("Sheet1")
Set ToWS = Sheets("Sheet2")
Application.EnableEvents = False
Application.ScreenUpdating = False
With Target
If .Address = FromWS.[B3].Address Then
.Copy
With ToWS.[B10]
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = False
End If
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

---
Click on the worksheet tab, select View Code, and paste
this into the window.

HTH
Jason
Atlanta, GA