How to keep cell formatting while referencing other sheet cells?
Generally, the cell only keeps the cell value while referencing other cell, but in this article, I introduce a VBA code to keep the cell values and formatting while referencing another cell, and the cell values and formatting changes as the reference cell changes as below screenshot shown.
Keep cell formatting while referencing another cell with VBA
Keep cell formatting while referencing another cell with VBA
To handle this job, you just need to run below code.
1. Enable the workbook you use, press Alt + F11 key to enable Microsoft Visual Basic for Application window, and double click on the sheet name which you will place the reference cell in Project-VBAProject pane to show a blank script. In this case, I want to refer to cell A1 in the Sheet1. See screenshot:
2. Paste below code to the script, and in the VBA code, specify the cell references as you need.
VBA: Keep formatting and values while referencing another cell
Private Sub Worksheet_Activate()
'UpdatebyExtendoffice20101024
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("A1")) Is Nothing Then 'Range("A1") the reference cell
Target.Copy
ActiveWorkbook.Sheets("Sheet2").Range("B1").PasteSpecial xlPasteAllUsingSourceTheme
'Range("B1")the cell linked to reference cell,ActiveWorkbook.Sheets("Sheet2")the sheet which contains linked cell
Application.CutCopyMode = False
Target.Select
End If
Application.EnableEvents = True
End SubNote: A1 is the reference cell, B1 in Sheet 2 is the cell you want to link to the reference cell and keep values and formatting with A1 in Shee1.
Then when you change the values or formatting in cell A1 in Sheet1, the cell B1 in Sheet2 will be changed while clicking at the reference cell twice.
Count by Color
|
| In some cases, you may have a range of calues with multiple colors, and what you want is to count/sum values based on same color, how can you quickly calculate? With Kutools for Excel's Count by Color, you can quickly do many calculations by color, and also can generate a report of the calculated result. |
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
