Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Linking cell formatting

0

Need to link cells (including cell formatting). In addition to referencing the value or formula from the source cell, I want the linked (dependent) cell to also follow the same formatting as the source cell (as the source cell formatting may change). 

Answer
Discuss

Discussion

Hi jkexcel and welcome to the Forum.

As Willie says (in his Answer), you didn't upload a representative Excel file so we can't see how many linked cells you have and whether they are on the same sheet, different sheets or different workbooks. Solutions other than Willie's general (but workable) Answer might be more appropriate.

To upload an Excel file, edit your original question and use the Add Files.. . button to attach it
John_Ru (rep: 6297) Jun 23, '24 at 9:36 am
Add to Discussion

Answers

0
Selected Answer

 Hello jkexcel and welcome to the forum,

You haven't uploaded a sample file so I can only provide a generalized answer. This can be achieved using VBA and the Worksheet_Change event. You will need to have separate code for each pair of linked cells.

The code below is for the linked pair of "B2" (source) and "C6" (linked/dependant). How it works is: if the value in "B2" changes then the formating of "B2" will be copied to "C6". The focus will stay on "B2".

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("B2") Then
    Target.Copy
        Range("C6").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone
        Application.CutCopyMode = False
End If
Target.Select

End Sub

This code goes in the Worksheet code window.

Update - June 23/24

If there are several pairs of Source / Linked cells then you could use the following code in the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address

    Case "$B$2"
        Target.Copy
            Range("C6").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone
            Application.CutCopyMode = False
    
    Case "$D$3"
        Target.Copy
            Range("C10").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone
            Application.CutCopyMode = False
        
End Select

Target.Select

End Sub

Just create a "Case" for each pair of cells.

If this solves things for you, please mark my answer as selected.

Cheers   :-)

Discuss

Discussion

Thank you!
jkexcel (rep: 2) Jun 25, '24 at 1:31 am
Glad I was able to help, and thank you for selecting my answer.
WillieD24 (rep: 567) Jun 25, '24 at 12:11 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login