Sabrina Ayon
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 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. 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.ColorThose lines of code link each cell’s color to each cell.
End SubThis 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.
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
- How to Link Cells in Same Excel Worksheet (4 Quick Ways)
- How to Link Tables in Excel (3 Easy Methods)
- How to Link Two Cells in Excel (6 Methods)
- Keep Formatting in Excel When Referencing Cells (2 Methods)
- How to Link Cells for Sorting in Excel (5 Methods)
- How to Link Multiple Cells in Excel (4 Methods)
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.