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.
Hi Sabrina,
This doesn’t work for a cell colour set by conditional formatting as that is not transferred in the suggested way, but is fine if one sets the cell colour manually. My aim is to have a matrix table where the performance text is the lead column, a bunch of cells that talk about how it is going, and a (small) scoring cell off to the right that gives a rating for this particular set of tasks. At the bottom of the scoring column (of the 17 or so rows) is the max score, so it is easy to conditional format on a 3 color scale between red, amber and green for each score cell in the scores range. I am trying to get the main Description text field to be the same color as that determined by the range based relative color, so one can tell at a glance where the topics are at. Or maybe the whole row would be good, with light colors. Do you know a way to transfer colors that have been set by conditional formatting? Thank you.
Hello Vince,
You’re absolutely right—conditional formatting colors don’t get transferred in the same way as manually set cell colors. Unfortunately, Excel doesn’t provide a built-in function to directly extract conditional formatting colors. However, you can use VBA to achieve this.
A possible approach is to use a VBA macro that reads the color of the conditionally formatted cell and applies it to another cell (or the entire row). If you’re interested, I can share a VBA script to help with this.
Let me know if you’d like the code!
Regards
ExcelDemy