get same format from referenced cell

  • Thread starter Thread starter bobby
  • Start date Start date
B

bobby

How do you get the format of a referenced cell to
transfer with the data?
 
Hi
this is not possible for Excel functions. Formulas can only return
values not formats
 
using VBA. Put this in your worksheet code module (right-click on the
worksheet tab and choose view code):

Assume the formula is in Sheet1 cell A1 and the referenced cell is in,
Sheet2 cell J10:


Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Sheets("Sheet2").Range("J10").Copy
Range("A1").PasteSpecial Paste:=xlFormats
Application.EnableEvents = True
End Sub

Note that this won't immediately update if the format in Sheet2!J10
changes - there's no event fired by a format change. You'll need to
press F9 or otherwise cause a calculation.
 
Ozzie,

It is automatic when you copy, but not when there is just a link between
dependent cell A1 ( =J10) and source (J10)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

bold text of referenced cell show in formula cell 1
Multiply Cell Values which include text units 6
countifs Function not giving desired result with date criteria 2
Highlight cells 1
conditional formatting # 1
formula cell format change 3
Excel Shortcut to go to the page/document referenced within a cell in excel 0
Referencing text in merged cells 1

Back
Top