How to Keep Formatting in Excel When Referencing Cells

The sample dataset contains two columns with names and ages. The header and data have different cell colors.

how to keep formatting in excel when referencing cells

Method 1 – Keeping the Format in Excel When Referencing Cells using the Copy and Paste Feature

Copy data and use the Paste Special command.

Steps:

Select B4:B10 and press CTRL+C.

Select a cell to paste this column. Here, E4.

Right-click E4 and select Linked Picture in Paste Special.

You will see B4:B10 in column E with the same formatting. You can also see the cell reference of the copied column in the formula bar.

Method 2 – Using VBA to Keep the Formatting in Excel When Referencing Cells

Utilize Visual Basic for Application (VBA).

Steps:

Open Visual Basic in the Developer Tab.

VBA will open. Double-click to open Sheet4 (here).

Enter the following code.

Private changing As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = [B4].Address Or changing Then 
    Exit Sub
    changing = True
    [B4].Copy [E4]
    changing = False
End Sub

An IF statement is used to check whether the cell reference has an address. The address is B4 and will be copied to E4.

Save the code by pressing CTRL+S and go to the sheet (Sheet4).
Copy B4 and Paste it into E4.

Enter new data in B4: change the heading to Name of Players.
Press ENTER and you will see the title in E4.

Practice Section

Practice here.

Download Practice Workbook

Keep the Format While Referencing Cells.xlsm

Further Readings

<< Go Back To Excel Link Cells | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Application.EnableEvents = False ' Check if the changed range is in Sheet1 A1:A67 If Not Intersect(Target, Sheets("Sheet1").Range("A1:A67")) Is Nothing Then ' Update data in Sheet2 A26:A85 Sheets("Sheet2").Range("A26:A85").Value = Sheets("Sheet1").Range("A1:A67").Value ' Copy formatting from Sheet1 A1:A67 to Sheet2 A26:A85 Sheets("Sheet1").Range("A1:A67").Copy Sheets("Sheet2").Range("A26:A85").PasteSpecial Paste:=xlPasteFormats Application.CutCopyMode = False End If Application.EnableEvents = True On Error GoTo 0 End Sub

6 Comments

Julie Nov 7, 2022 at 6:12 PM

Thank you Nahian. However, what about referencing cells from an external spreadsheet? I basically want to copy the cell’s value/text, including the formatting, into another spreadsheet. The values are easy enough but I want the formating to be copied too.

The original spreadsheet is basically a calender with several apartments booking details. I need to extract one apartment details (each day of the month, each month in one row) to put into a separate spreadsheet.

This is so that I can share this new spreadsheet with the owner of this apartment without them being able to see the original spreadsheet that has all the other information.

I can do this easily however, the formatting is not copied over.
- Reply
  Nahian Nov 8, 2022 at 2:25 PM
  
  Thank you Julie for reaching out. The first method can be done to solve your problem. Just copy the data and paste this as linked picture into a new spreadsheet. Any change in your main spreadsheet will automatically be updated in the new sheet.
Z Apr 14, 2023 at 11:29 PM

Excel crashes after running the VBA.
- Reply
  Akib Bin Rashid Apr 17, 2023 at 12:45 PM
  
  Hi Z
  I have checked the code in different Excel versions on different laptops. It’s working fine. This is an event-driven macro. So, on changing the cell’s value, you should get the output. Maybe due to some compatibility issues or external problems, you are facing this hiccup.
  Thank you
Lea Feb 21, 2024 at 7:38 PM

Hi, Would this work on ranges of cells so that one VBA can be written for all rather than one cell at a time.
For example sheet 1 range A1:A67 as reference cells, copied into sheet 2 range A26:A85 cells?
- Reply
  Lutfor Rahman Shimanto Feb 22, 2024 at 4:37 PM
  Hello Lea, thanks for reaching out. Here’s a solution for your query.
```
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Application.EnableEvents = False
    
    ' Check if the changed range is in Sheet1 A1:A67
    If Not Intersect(Target, Sheets("Sheet1").Range("A1:A67")) Is Nothing Then
        ' Update data in Sheet2 A26:A85
        Sheets("Sheet2").Range("A26:A85").Value = Sheets("Sheet1").Range("A1:A67").Value
        
        ' Copy formatting from Sheet1 A1:A67 to Sheet2 A26:A85
        Sheets("Sheet1").Range("A1:A67").Copy
        Sheets("Sheet2").Range("A26:A85").PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
    End If
    
    Application.EnableEvents = True
    On Error GoTo 0
End Sub
```
  Procedure:
  
  1. Right click on the Sheet1 tab of your workbook and select View Code.
  
  2. Paste the code and save the workbook.
  
  3. Now, if you do any formatting and insert a value in the range of A1:A67 of Sheet1, you can see the values and formatting get copied in the Sheet2.
  
  Regards
  Meraz Al Nahian
  ExcelDemy

How to Keep Formatting in Excel When Referencing Cells – 2 Methods

Method 1 – Keeping the Format in Excel When Referencing Cells using the Copy and Paste Feature

Method 2 – Using VBA to Keep the Formatting in Excel When Referencing Cells

Practice Section

Further Readings

Leave a reply Cancel reply