News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • include formatting when referencing cell.

    Posted on WSskipro Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function MS Excel and spreadsheet help include formatting when referencing cell.

    This topic contains 10 replies, has 7 voices, and was last updated by  WSmrfisher 2 years, 6 months ago.

    • Author
      Posts
    • #492932 Reply

      WSskipro
      AskWoody Lounger

      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.

    • #1434373 Reply

      WSsdckapr
      AskWoody Lounger

      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

    • #1434548 Reply

      WSskipro
      AskWoody Lounger

      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.”

    • #1434642 Reply

      Maudibe
      AskWoody_MVP

      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  [COLOR=”#008000″]’COMMENT OUT IF REFERRED CELL VALUE NOT REQUIRED[/COLOR]
          [COLOR=”#008000″]’rng.Value = “”  ‘ACTIVATE IF REFERRED CELL VALUE NOT REQUIRED[/COLOR]
      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 = “”

      Attachments:
      • #1435117 Reply

        WSskipro
        AskWoody Lounger

        Maud,
        Good job, does what I need. Thank you.

        • #1591665 Reply

          WSmustapha
          AskWoody Lounger

          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

    • #1591670 Reply

      Maudibe
      AskWoody_MVP

      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
      
      Attachments:
      • #1591686 Reply

        WSmustapha
        AskWoody Lounger

        excellent, thank you very much!

        • #1591715 Reply

          zeddy
          AskWoody_MVP

          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

          Attachments:
      • #1594102 Reply

        WSspandrd
        AskWoody Lounger

        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

        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
        
        Attachments:
      • #1594331 Reply

        WSmrfisher
        AskWoody Lounger

        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

    • #1594332 Reply

      RetiredGeek
      AskWoody MVP

      Mrfisher, welcome to the Lounge as a new poster! :cheers:

      Simply include the sheet name: [noparse]=sheetname!cellreference[/noparse]
      Example: [noparse]=Sheet3!$A$4[/noparse]

      If the sheet name has a space in it enclose in quotes: [noparse]=’My Data’!$A$4[/noparse]

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1594333 Reply

        WSmrfisher
        AskWoody Lounger

        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!

        Mrfisher, welcome to the Lounge as a new poster! :cheers:

        Simply include the sheet name: [noparse]=sheetname!cellreference[/noparse]
        Example: [noparse]=Sheet3!$A$4[/noparse]

        If the sheet name has a space in it enclose in quotes: [noparse]=’My Data’!$A$4[/noparse]

        HTH :cheers:

        • #1595088 Reply

          WSkdurk
          AskWoody Lounger

          Also looking for the answer to live formatting referencing across sheets. Any luck?

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: include formatting when referencing cell.

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.