Results 1 to 14 of 14
  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 21:24.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,793
    Thanks
    139
    Thanked 705 Times in 639 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 12:47. Reason: add file

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

    boobounder (2016-07-02),KBurns (2015-09-09),mustapha (2017-02-22),superkidsnurse (2014-12-26)

  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.

  7. #6
    New Lounger
    Join Date
    Feb 2017
    Posts
    2
    Thanks
    3
    Thanked 0 Times in 0 Posts

    live formatting links

    Thank you, that was very helpful.

    Is it possible to create a live link? for example, if I change the formatting of cell A1, the formatting of all the cells linked to A1 will update as well.

    Regards,
    Mustapha

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,793
    Thanks
    139
    Thanked 705 Times in 639 Posts
    Hi Mustapha,

    Welcome to the forum. Here is the revised workbook that will additionally update formula dependent cells if any format changes are made to the precedent cell. Note: this works only for the cells on the same sheet but with a little more coding, it could be extended to additional sheets.

    HTH,
    Maud

    In Sheet1 Worksheet module, place the following code and keep any existing code from the previous sample as is:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Worksheets("Hidden")
    On Error Resume Next
    Application.EnableEvents = False
        Range(.Range("Z1").Value).Copy
        Range(.Range("Z1").Value).Dependents.Select
    Application.EnableEvents = True
    If Err = 0 Then
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End If
    .Range("Z1") = Target.Address
    End With
    Application.CutCopyMode = False
    End Sub
    Attached Files Attached Files

  9. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    kdurk (2017-05-16),mustapha (2017-02-22)

  10. #8
    New Lounger
    Join Date
    Feb 2017
    Posts
    2
    Thanks
    3
    Thanked 0 Times in 0 Posts
    excellent, thank you very much!

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,188
    Thanks
    152
    Thanked 591 Times in 561 Posts

    live formatting with the Excel Camera

    Hi mustapha

    An excellent method from Maud indeed.

    Another method is to use the built-in Excel-camera.
    See attached file.

    The Excel-camera allows you to 'point' a 'live-colour-CCTV-camera' at any cell, or any range of cells, anywhere in the workbook. Whenever any changes are made in those cells, they are shown in your CCTV location. Very neat method I have used a lot. No vba required. And you can either 'float' your CCTV picture anywhere you like, or 'anchor' and size-it to cell(s)

    zeddy
    Attached Files Attached Files

  12. The Following User Says Thank You to zeddy For This Useful Post:

    mustapha (2017-02-23)

  13. #10
    New Lounger
    Join Date
    Mar 2017
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    got error while copy-pasting code

    hi

    Gr8 coding... )

    I tried to copy code from Revised2 version of your excel sheet, to my own excel sheets, but whenever run, it shows "Run time error 9" as per attachment.

    What is the change I need to make to my "range"? Can you please guide...?

    rgds,
    spandrd

    Quote Originally Posted by Maudibe View Post
    Hi Mustapha,

    Welcome to the forum. Here is the revised workbook that will additionally update formula dependent cells if any format changes are made to the precedent cell. Note: this works only for the cells on the same sheet but with a little more coding, it could be extended to additional sheets.

    HTH,
    Maud

    In Sheet1 Worksheet module, place the following code and keep any existing code from the previous sample as is:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Worksheets("Hidden")
    On Error Resume Next
    Application.EnableEvents = False
        Range(.Range("Z1").Value).Copy
        Range(.Range("Z1").Value).Dependents.Select
    Application.EnableEvents = True
    If Err = 0 Then
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End If
    .Range("Z1") = Target.Address
    End With
    Application.CutCopyMode = False
    End Sub
    Attached Images Attached Images

  14. #11
    New Lounger
    Join Date
    Apr 2017
    Posts
    2
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Maud,
    I'm new to the forum and not very experienced with code. Your posts have been very helpful so far, but I need to reference cells on a different sheet, and I don't have a clue how to do that. Can you point me in the right direction?
    Thanks,
    mrfisher

  15. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,781
    Thanks
    403
    Thanked 1,554 Times in 1,407 Posts
    Mrfisher, welcome to the Lounge as a new poster!

    Simply include the sheet name: =sheetname!cellreference
    Example: =Sheet3!$A$4

    If the sheet name has a space in it enclose in quotes: ='My Data'!$A$4

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  16. #13
    New Lounger
    Join Date
    Apr 2017
    Posts
    2
    Thanks
    0
    Thanked 1 Time in 1 Post

    Not Quite the Answer

    Hi there,

    Thanks for the quick response, but my question was related to a specific code that had been provided for updating the code for maintaining source formatting in a referenced cell. This was what I pulled from the previous forum string - text in bold and italics is where I need clarification on referencing a different sheet:

    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 = ""



    Here is the revised workbook that will additionally update formula dependent cells if any format changes are made to the precedent cell. Note: this works only for the cells on the same sheet but with a little more coding, it could be extended to additional sheets.

    In Sheet1 Worksheet module, place the following code and keep any existing code from the previous sample as is:
    Code:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Worksheets("Hidden")
    On Error Resume Next
    Application.EnableEvents = False
    Range(.Range("Z1").Value).Copy
    Range(.Range("Z1").Value).Dependents.Select
    Application.EnableEvents = True
    If Err = 0 Then
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    End If
    .Range("Z1") = Target.Address
    End With
    Application.CutCopyMode = False
    End Sub


    Any help would be great. Thanks!


    Quote Originally Posted by RetiredGeek View Post
    Mrfisher, welcome to the Lounge as a new poster!

    Simply include the sheet name: =sheetname!cellreference
    Example: =Sheet3!$A$4

    If the sheet name has a space in it enclose in quotes: ='My Data'!$A$4

    HTH

  17. The Following User Says Thank You to mrfisher For This Useful Post:

    kdurk (2017-05-16)

  18. #14
    New Lounger
    Join Date
    May 2017
    Posts
    6
    Thanks
    3
    Thanked 1 Time in 1 Post
    Also looking for the answer to live formatting referencing across sheets. Any luck?

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
  •