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.


Download Practice Workbook

You can download the workbook and practice with them.


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

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.f

Step-by-Step Guidelines to Automatically Link a Cell Color to Another in Excel


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.

Step-by-Step Guidelines to Automatically Link a Cell Color to Another in Excel

  • 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.

Step-by-Step Guidelines to Automatically Link a Cell Color to Another in Excel

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. And 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.

Read More: How to Link Multiple Cells from Another Worksheet in Excel (5 Easy Ways)


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.

Step-by-Step Guidelines to Automatically Link a Cell Color to Another in Excel


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 would 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. And 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 we 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.


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. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

2 Comments
  1. 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.

Leave a reply

ExcelDemy
Logo