Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    include formatting when referencing cell.

    How can I include a cells formatting when referencing it.
    B1 to equal or reference A1; B1 will have same formatting as A1. Mostly across worksheets.

    I would like it to include Font [type,size,color,{bold/underline/italicize}] and border mainly, fill second, others lastly.

    Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The easisest way is to to copy and paste which by default includes the formatting. If you do that first you will get the formatting, then you can immediately (so the clipboard is not cleared) do a paste-special, paste links, and the contents will be a reference to the source from the value.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Steve,
    Thanks, but I need it to automatically come across when I reference a cell from one worksheet to another, like you helped me with including comments when referencing cells across worksheets.
    Formatting of A1 on worksheet1 to carryover to B1 [or wherever] on worksheet2.
    B1[worksheet2]='worksheet1'!A1 [bold/red/bordered]
    -- then B1[worksheet2] would have same value plus same formatting [bold/red/bordered]

    Hope this explains better.

    I tried your suggestion but I get an error message, "MS Excel cannot paste the data."
    Last edited by skipro; 2014-01-18 at 22:24.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Skipro,

    Place the following code in a standard module:
    Code:
    Public Sub CopyFormat(rng As Range)
    On Error Resume Next
    If rng.Value = "" Then Exit Sub
    If Left(rng.Value, 1) = "#" Then
        Ref = Mid(rng.Value, 2, Len(rng.Value) - 1)
        Range(Ref).Copy
        rng.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
            , SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        rng.Formula = "=" & Ref  'COMMENT OUT IF REFERRED CELL VALUE NOT REQUIRED
        'rng.Value = ""  'ACTIVATE IF REFERRED CELL VALUE NOT REQUIRED
    End If
    End Sub
    Place the following code in the Worksheet_Change event routine of each sheet:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    CopyFormat Target
    End Sub
    In the calling cell enter the formula but replace the "=" with a pound sign "#" (without the quotes):

    Instead of the formula for A5 being =A1 it will be #A1
    Instead of the formula for Sheet2 cell A1 being =Sheet1!A1, it will be #Sheet1!A1

    It can be used to carry the value and the formatting from cell to cell on the same sheet or between different sheets. It will also carry the comment from the referred cell as well. If you want to copy just the formatting without the value then change rng.formula = "=" & Ref to Rng.value = ""
    Attached Files Attached Files
    Last edited by Maudibe; 2014-01-19 at 13:47. Reason: add file

  5. The Following 3 Users Say Thank You to Maudibe For This Useful Post:

    boobounder (2016-07-03),KBurns (2015-09-09),superkidsnurse (2014-12-27)

  6. #5
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Maud,
    Good job, does what I need. Thank you.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •