How to Automatically Link a Cell Color to Another in Excel

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.

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

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