How to Automatically Link a Cell Color to Another in Excel

While working with Microsoft Excel, colors assist us in better visualizing our data by allowing us to visually distinguish groupings of similar data. Often we need to link a cell to another cell in Excel. To make the dataset more user-friendly, we can color those linked cells. In this article, we will demonstrate how to automatically link a cell color to another in Excel.

How to Automatically Link a Cell Color to Another in Excel: Step-by-Step Procedures

In Excel, to make the dataset more visually appealing, we can use colors in cells. To link a cell color to another automatically we are going to use the following dataset. The dataset contains some subjects in column B and marks of a student for each subject in column D. Suppose we want to automatically link cell range B5:B8 color to another cell range D5:D8.

We can do this using Excel VBA Macros. With Excel VBA users can easily manipulate the data as per their preferences. We can use the VBA to automatically link a cell color to another cell in Excel. Let’s follow the procedures to link a cell color to another using Excel VBA. The code we are using to automatically link a cell color to another will change the color automatically while changing the referencing cell.

Step 1: Open Visual Basic Editor of Excel VBA

Firstly, we need to go to the Developer tab from the ribbon.
Secondly, click on Visual Basic to open the Visual Basic Editor from the Code category, where we will write down our codes. Or press Alt+F11 to open the Visual Basic Editor.

Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.

Step 2: Write an Excel VBA Code to Link a Cell Color to Another

This will open up the visual basic window.
After that, write down the VBA code there.

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

Further, press the Ctrl+S to save the code. Make sure while saving the workbook, your file is Marco enable.

VBA Code Explanation

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

The Private Sub is a portion of code that does not return any value but is utilized to handle the work in the code. Subprocedure is another name for it. So we name our procedure Worksheet_SelectionChange. The ByVal Target As Range line, which employs the Variable called Target, is used in the preceding. The Range that will cause an operation is known as the 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.Color

Those lines of code link each cell’s color to each cell.

End Sub

This line is for closing the procedure.

Step 3: Final Output

Finally, if you go back to your worksheet. You can see that the cells automatically change their color. If you change the color of the referencing cell B5 or B6 or B7 or B8 this will automatically change the color of the cells respectively D5, D6, D7, D8.

Conditional Formatting to Color a Cell in Excel

The spreadsheets may have the capacity to organize themselves instantaneously as users operate upon them. This ensures that when the cells alter their format, everything we do inside the spreadsheet will be pushed to our notice more quickly. In Excel, we can easily do that using conditional formatting. We can use conditional formatting to color a cell in Excel.

To change the color of the cells we are using the dataset below. The dataset contains some subjects in column B and the marks of a student for each subject in column C. Now, suppose we want to color the cells whose subjects’ marks are greater than 90.

For this, we have to follow some simple steps down. So, let’s look at those steps to color a cell by applying conditional formatting.

STEPS:

First, go to the Home tab from the ribbon.
Second, under the Styles group, go to the Conditional Formatting drop-down menu.
Third, click on Greater Than from the Highlight Cells Rules drop-down menu.

This will appear in the Greater Than dialog box.
In that dialog box, type the number which will fulfill the condition. So we type 90.
Then, select the color, and select Light Red Fill with Dark Red Text.
Now, click on the OK button to close the dialog box.

And, that’s it. All the numbers that are greater than 90 are now colored.

Download Practice Workbook

You can download the workbook and practice with them.

Link Cell Color.xlsm

Conclusion

The above steps will assist you to Link a Cell Color Automatically to Another in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.

<< Go Back To Excel Link Cells | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Kristin Jun 21, 2022 at 3:46 PM

How would you do this if the cells you are wanting to link are in different sheets?
- Reply
  Sabrina Ayon Aug 14, 2022 at 2:30 PM
  
  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.