To demonstrate how to link a cell color to another automatically, we’ll use the following dataset containing some subjects in column B and corresponding marks in column D.
Let’s automatically link the color in cell range B5:B8 to another cell range D5:D8 using Excel VBA Macros. The code will change the color automatically when the referencing cell changes.
Step 1 – Open Visual Basic Editor
- Go to the Developer tab on the ribbon.
- Click on Visual Basic to open the Visual Basic Editor, or press Alt+F11.
- Alternatively, right-click on your worksheet and from the context menu select View Code.
Step 2 – Write VBA Code to Link a Cell Color to Another
- Enter the following code in the module window:
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("D5").Interior.Color = Me.Range("B5").Interior.Color
Me.Range("D6").Interior.Color = Me.Range("B6").Interior.Color
Me.Range("D7").Interior.Color = Me.Range("B7").Interior.Color
Me.Range("D8").Interior.Color = Me.Range("B8").Interior.Color
End Sub- Press Ctrl+S to save the code using the file-type Macro-enabled.
VBA Code Explanation
Private Sub Worksheet_SelectionChange(ByVal Target As Range)The Private Sub or subprocedure names our procedure Worksheet_SelectionChange.
ByVal Target As Range sets the Range in the variable Target.
Me.Range("D5").Interior.Color = Me.Range("B5").Interior.Color
Me.Range("D6").Interior.Color = Me.Range("B6").Interior.Color
Me.Range("D7").Interior.Color = Me.Range("B7").Interior.Color
Me.Range("D8").Interior.Color = Me.Range("B8").Interior.ColorThese lines link each cell’s color to the color of a corresponding cell.
End SubThis line closes the procedure.
Step 3 – Final Output
- Go back to your worksheet.
If you change the color of the referencing cells B5, B6, B7 or B8, this will automatically change the color in cells D5, D6, D7, D8 respectively.
Conditional Formatting to Color a Cell in Excel
We can alternatively use conditional formatting to color a cell in Excel.
The dataset below contains some subjects in column B and marks for each subject in column C. Let’s color the cells with marks greater than 90.
Steps:
- Go to the Home tab on the ribbon.
- Under the Styles group, open the Conditional Formatting drop-down menu.
- Click on Greater Than from the Highlight Cells Rules drop-down menu.
- In the Greater Than dialog box that opens, enter the number which will fulfill the condition, here 90.
- Select the color Light Red Fill with Dark Red Text.
- Click on OK.
All the numbers greater than 90 are now colored.
Download Practice Workbook
Related Articles
- How to Link Cells in Same Excel Worksheet
- How to Link Tables in Excel
- How to Link Two Cells in Excel
- Keep Formatting in Excel When Referencing Cells
- How to Link Cells for Sorting in Excel
- How to Link Multiple Cells in Excel
- How to Stop Cell Mirroring in Excel
- How to Link Multiple Cells from Another Worksheet in Excel
<< Go Back To Excel Link Cells | Linking in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
How would you do this if the cells you are wanting to link are in different sheets?
Hello, KRISTIN!
I’m sorry to say that, it won’t work for linking a cell color to a different sheets.
Even we can not do that actually. But you can copy the color format from a sheet cell and paste that into the differnet sheet.