Equal = both value and format of another cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I modify the formula of equals (=) so that it equals both the value
and formatting of another cell?
 
Right click sheet tab>view code>copy/paste this. You may want to restrict to
certain cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range(Cells.Find(Target).Address).Copy
Target.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub
 
Is the formatting likely to change then? if not just copy, paste special -
formats.


If yes the format will be changing then cant you put conditional formatting
on the destination cell the same as the 'linked to' cell?

hope im understanding you correctly here.
 
Hi,

If I understand your question I can't get the previous solution to work so
try this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err1
Application.EnableEvents = False
X = Target.Dependents.Address
Target.Copy
Range(X).PasteSpecial xlPasteFormats
Err1:
Application.EnableEvents = True
End Sub

Note that this applies to All cells on the current worksheet that have
formulas. If you want to limit those to certain cells please let us know.
Also changing the cells format does not trigger these macros, only change the
entry in the cell. To handle a cell formatting change without changing the
value in the cell will present more of a challenge, so let us know.
 
Back
Top