Note: The other languages of the website are Google-translated. Back to English

How to automatically link a cell color to another in Excel?

While using Microsoft Excel, do you know how to link a cell color to another automatically? This article will show you method to achieve it.

Automatically link a cell color to another with VBA code


Automatically link a cell color to another with VBA code

Supposing you want to link the fill color of cell A1 to C1, when changing the fill color of A1, the color of C1 will turn to the same automatically. Please do as follows.

1. Right click the sheet tab you need to link a cell color to another, and then click View Code from the right-clicking menu.

2. In the opening Microsoft Visual Basic for Applications window, please copy and paste the below VBA code into the Code window.

VBA code: Auto link a cell color to another in Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Range("C1").Interior.Color = Me.Range("A1").Interior.Color
End Sub

Note: you can change the cells reference in the code as you need.

3. Go ahead to press the Alt + Q keys at the same time to close the Microsoft Visual Basic for Applications window.

From now on, when changing the fill color of cell A1, the fill color of cell C1 will be changed to the same color automatically.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (45)
Rated 4.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
what about between sheets in the same workbook - please advice how does the formula change? thanks!
This comment was minimized by the moderator on the site
Great tutorial. Thanks :) !!!!
This comment was minimized by the moderator on the site
yes I would like to know how to mirror a color to another sheet??? I have a formula in both boxes but I would like the color of the one to mirror the color on the mainsheet when it changes. i.e... Training Date sheet is w/in 30 days and box changes to red; the corresponding box on mainsheet, the box with the "X" changes to red also.
This comment was minimized by the moderator on the site
Hi Jessica,
What do you mean box? Textbox?
This comment was minimized by the moderator on the site
Is there a way to do this if the two cells are in different workbooks?
This comment was minimized by the moderator on the site
Hi Dustin,
Can't handle different workbooks. Thank you for your comment.
This comment was minimized by the moderator on the site
I saw up here, in the explanation, how to for one cell, how to do for few cells?
This comment was minimized by the moderator on the site
Hi Rogerio,
Do you mean link the fill color of a cell to multiple cells at the same time?
This comment was minimized by the moderator on the site
y su el la celda A1 tiene un formato condicional ?
This comment was minimized by the moderator on the site
If the cells are un different sheets?
This comment was minimized by the moderator on the site
Hi Mario,
Supposing you want to link the color of cell A1 in current worksheet to range B1:J19 in Sheet2, please apply the below VBA code. After changing the fill color of A1, please click on another cell in current worksheet to activate the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range
Dim xStrAddress As String
xStrAddress = "Sheet2!$B$1:$J$19"
Set xRg = Application.Range(xStrAddress)
xRg.Interior.Color = Me.Range("A1").Interior.Color
End Sub
This comment was minimized by the moderator on the site
That worked. How would it work if you have A1 in Sheet1 changes to happen to B1 in Sheet2, but also A2 in Sheet1 changes to happen to B2 in Sheet2?
This comment was minimized by the moderator on the site
Hi Can anyone help with above question asap, I am in dire need of it and running out of time.
This comment was minimized by the moderator on the site
Hi,
The below VBA code can help you solve the problem. Thank you for your comment.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range
Dim xStrAddress As String
xStrAddress = "Sheet2!$B$1"
Set xRg = Application.Range(xStrAddress)
xRg.Interior.Color = Me.Range("A1").Interior.Color
xStrAddress = "Sheet2!$B$2"
Set xRg = Application.Range(xStrAddress)
xRg.Interior.Color = Me.Range("A2").Interior.Color
xStrAddress = "Sheet2!$B$3"
Set xRg = Application.Range(xStrAddress)
xRg.Interior.Color = Me.Range("A3").Interior.Color
End Sub
This comment was minimized by the moderator on the site
Unfortunately it didn't work for me. I'm wondering if it is because the originating cell is a list with a conditional formatting rule to change background colors depending on what option is picked in the list.
This comment was minimized by the moderator on the site
Hi Amanda,
This code doesn't work for the fill color assigned by conditional formatting rule. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Is it possible to link the color from one range on one sheet to another range of the same size on a different sheet? For example, I have alternating colors every couple of rows, and I want that color scheme to copy over to another sheet.
This comment was minimized by the moderator on the site
Hi Charles,
The below code can do you a favor. If you want to link the color from range A1:A19 on Sheet1 to the same range "A1:A19" on Sheet2, please copy the code into the Sheet1's Code window, after that, click on any cell on Sheet1 to activate the code. Hope I can help. Thank you for your comment.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range
Dim xStrAddress As String
xStrAddress = "Sheet2!$A$1:$A$19"
Set xRg = Application.Range(xStrAddress)
xRg.Interior.Color = Me.Range("A1:A19").Interior.Color
End Sub
This comment was minimized by the moderator on the site
Hi,
this code seems to work if I reference one cell but if I input a range, the range on sheet 2 goes black instead of coping the interior color.

any ideas why that might be happening?
This comment was minimized by the moderator on the site
Hi Joey,
Sorry for the mistake. Try this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range
Dim xCRg As Range
Dim xStrAddress As String
Dim xFNum As Integer
xStrAddress = "Sheet2!$A$1:$A$10"
Set xRg = Application.Range(xStrAddress)
Set xCRg = Me.Range("$A$1:$A$10")
On Error Resume Next
For xFNum = 1 To xRg.Count
xRg.Item(xFNum).Interior.Color = xCRg.Item(xFNum).Interior.Color
Next
End Sub
This comment was minimized by the moderator on the site
Hi Crystal,
Thanks very much. Works perfectly!
You’re amazing! O_o
This comment was minimized by the moderator on the site
Hi Crystal!
What does the same code look like if I want it to copy to both Sheet2 and Sheet3 at the same time?
This comment was minimized by the moderator on the site
Hi can anybody answer this question. I'm trying to do exactly this and having no luck.
This comment was minimized by the moderator on the site
this does not work for me. I keep getting error about "compile error, ambiguous name detected"....
This comment was minimized by the moderator on the site
this does not work for me. may cell range is not the same on both sheets. can you help advise what to do or how to adjust code pls?
This comment was minimized by the moderator on the site
Hi LG,
There are two lines you can modify: the range "$A$1:$A$10" of the eighth line, and the "Sheet2!$A$1:$A$10" of the sixth line, which indicate you will link the fill color of range A1:A10 in a worksheet (supposing Sheet1, and the code should be added to this sheet code window) to the same range in Sheet2.
You can specify two different ranges as you need. And please make sure the sheet name you type in the sixth line is an existing sheet name.
This comment was minimized by the moderator on the site
I am using this to create weaving patterns. I would like to have several sets of groups so I only have to change one cell to see what will happen in my pattern. It worked from cell a1 to c1 but when I tried to add a new set a2 to c1 it did not. I may misunderstand, but I copied the same formula below the first one and changed the cell references. Is that the problem? Is there another way?
This comment was minimized by the moderator on the site
This one worked for me when pasting in a second sheet reference--but I had to make a modification, I will show the modification below so you can see Crystal's first version, then mine with modification that worked when I needed to include two sets instead of one. This is the one Crystal gave to Joey after he said the target range turned black when he tried it:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range
Dim xCRg As Range
Dim xStrAddress As String
Dim xFNum As Integer
xStrAddress = "Sheet2!$A$1:$A$10"
Set xRg = Application.Range(xStrAddress)
Set xCRg = Me.Range("$A$1:$A$10")
On Error Resume Next
For xFNum = 1 To xRg.Count
xRg.Item(xFNum).Interior.Color = xCRg.Item(xFNum).Interior.Color
Next
End Sub
My mod that worked in order to include two sets (e.g. reference different columns or rows or what have you) was to include this part:
On Error Resume Next
For xFNum = 1 To xRg.Count
xRg.Item(xFNum).Interior.Color = xCRg.Item(xFNum).Interior.Color
Next
after each of my "sets"
So, without modifying the sample sets that Crystal gave to be different from each other (which yours probably will be if you're trying to refer to different rows etc), it would look like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range
Dim xCRg As Range
Dim xStrAddress As String
Dim xFNum As Integer
xStrAddress = "Sheet2!$A$1:$A$10"
Set xRg = Application.Range(xStrAddress)
Set xCRg = Me.Range("$A$1:$A$10")On Error Resume Next
For xFNum = 1 To xRg.Count
xRg.Item(xFNum).Interior.Color = xCRg.Item(xFNum).Interior.Color
Next
xStrAddress = "Sheet2!$A$1:$A$10"
Set xRg = Application.Range(xStrAddress)
Set xCRg = Me.Range("$A$1:$A$10")
On Error Resume Next
For xFNum = 1 To xRg.Count
xRg.Item(xFNum).Interior.Color = xCRg.Item(xFNum).Interior.Color
Next
End Sub

with lines 6-8 and 13-15 being the "sets"and lines 9-12 and 16-19 being the code that has to repeat after each set.
I hope that makes sense because I know nothing about this, I just found a way to copy what was listed here that worked. The first time I tried having two sets by plugging in to the code as is, the first set turned the cells black and the second set worked and turned the target cells the right color. I finally figured out that in order to work, each set needed the line 9-12 code after it. This mod allowed appropriate colors to (in my case) transfer from two different columns on the origin sheet to corresponding cells within a given range on the target sheet.
This comment was minimized by the moderator on the site
How do I link a cell colors based on another cells value and color to be automatically linked.

Fort Example.



I have sheet 1 value is 898 and on sheet 2 value is 898 and that cell is colored pink. How can I link the same color to be the same based on the clue is sheet 2 to be linked to sheet 1 - so it can show the same color. But it would be a range; using the entire row to match its values and then link the colors.



Any help is greatly appreciated
This comment was minimized by the moderator on the site
Hi esad, i have same case with you currently, i would appreciate if you share
This comment was minimized by the moderator on the site
I am looking copy colors generated from conditional formatted table, to another table - automatically with a formula/function.Is this possible?
This comment was minimized by the moderator on the site
Hy,
I would like that when I manually change the color in column A sheet1 to automatically change the color in column A sheet2.
I'm asking for help. Thank you
This comment was minimized by the moderator on the site
Hi Ivana,Please try the below code.<div data-tag="code">Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updated by Extendoffice 20201127
Dim xRg As Range
Dim xCRg As Range
Dim xStrAddress As String
Dim xFNum As Integer
xStrAddress = "Sheet2!$A$1:$A$100"
Set xRg = Application.Range(xStrAddress)
Set xCRg = Me.Range("$A$1:$A$100")
On Error Resume Next
For xFNum = 1 To xRg.Count
xRg.Item(xFNum).Interior.Color = xCRg.Item(xFNum).Interior.Color
Next
End Sub
This comment was minimized by the moderator on the site
Hola buenas tardes. Como hacer que cuando seleccione una celda se active otra, y si selecciono otro cambie a otro celda con que este vinculada.Ejemplo: si alecciono A1 se active celda H20, y si doy otro clic se desactive. Se que seria mucho trabajo porque tendria que programar cada celda, pero no importa, es solo saber como hacerlo. Gracias por su apoyo!
This comment was minimized by the moderator on the site
hi the above code worked but wont work multiple times in one sheet? 
This comment was minimized by the moderator on the site
Hi all,

My issue is that my home page (1st sheet) contains a summary of information provided in the following sheets. All the relevant cells have been formatted to reflect the value of their corresponding cells in the other sheets.

so for example, My cell homepage cell F7 is already formatted so it copies the value of the corresponding cell in source sheet:
=quarter1!B15

Now, what i would really want is for the cells on my home page to also match the color i manually select for their corresponding (source) cell in another sheet. Is this possible using VBA coding?

I got the below code from another site but it only works if the cells are on the same sheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("C1").Interior.Color = Me.Range("A1").Interior.Color
End Sub

Is there a way i can tweak this code to refer to a cell on another sheet in the same workbook?

Thank you so much for your help!
This comment was minimized by the moderator on the site
Hi I wonder if you can help me please
I have a spreadsheet that has team members (17 of them) name in Row 2
In column H we would allocate a task to a team member
In column T we have the data to show what stage the team member is at for this task ( green/ Amber /Red)
What I would like to do is bring the colour for the task from col T to Col H with the name we would have entered

Any help would be greatly appreciated
This comment was minimized by the moderator on the site
Hi Ann,
Would you mind providing a screenshot of your data? Sorry for the inconvenience.
This comment was minimized by the moderator on the site
As per attached
The team names are in row 2 (R to AD)
Each row under the team name relates to the task in Col D
What I would like is when a name is entered in Col L it colours that cell with the colour from the team members col on that row
e.g. in Col L enter Emma it looks at Emma in W1 and brings over Green from W2. if it was Paula is would look at Paula in T1 and bring over amber from T2 etc
The name entered would still remain as example in L2 & 3

Thanks for your help
Not sure how to attaché screenshot or a FILE I can email it
This comment was minimized by the moderator on the site
Hi Ann,
I have tried some methods and still can't solve your problem. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Thanks for trying
This comment was minimized by the moderator on the site
Aqui la tiene en ingles


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range
Dim xCRg As Range
Dim xStrAddress As String
Dim xFNum As Integer
xStrAddress = ("Hoja2!A1")
Set xRg = Application.Range(xStrAddress)
Set xCRg = Me.Range("A1")
On Error Resume Next
For xFNum = 1 To xRg.Count
xRg.Item(xFNum).Interior.Color = xCRg.Item(xFNum).Interior.Color
Next
End Sub
This comment was minimized by the moderator on the site
I have found success using this line of code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range
Dim xCRg As Range
Dim xStrAddress As String
Dim xFNum As Integer
xStrAddress = "Sheet2!$A$1:$A$10"
Set xRg = Application.Range(xStrAddress)
Set xCRg = Me.Range("$A$1:$A$10")
On Error Resume Next
For xFNum = 1 To xRg.Count
xRg.Item(xFNum).Interior.Color = xCRg.Item(xFNum).Interior.Color
Next
End Sub

I would like to be able to use one range to influence several others within one section of code. I.E., if I change a color in $A$1:$A$10, it changes the color in $C$10:$C$19, $D$21:$D$30, and $F$10:$F$19. Is this possible? Thank you.
This comment was minimized by the moderator on the site
Hi all, anyone can help me with the same case with esad
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations