Copy cell colors to the same cell in other worksheets

riksutin

New Member
Joined
Mar 30, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi!

So I'm trying to figure out a way to copy cell interior colors from one worksheet to another.

Let's say I have an excel with 3 sheets: Worksheet 1 = WS1 and Worksheet 2 = WS2 and Worksheet 3 = WS3
These three worksheets are just basically copies of the first one.

So, if I have cell F4 in WS1 that I fill in as yellow, I want the cell F4 to be painted as yellow also in WS2 and WS3.
AND if I choose to change the cell color of F4 in WS1 from yellow to e.g. blue, I want that to also change the cell color of F4 in WS2 and WS3.

I am working with max. of five different colors, so not all colors need to be included.
The values in the cells don't matter, I just want to copy the interior color.

Question is: Is this possible and how would I go about it? I have very little experience in VBA...
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
635
Office Version
  1. 365
Platform
  1. Windows
Please, see if this solution can be used.
I've assumed WS2 and WS3 are tab sheet names.
A copy of the code below should go in WS1 module (right click on that tab sheet | View Code).
Afetr you've changed a cell color on WS1 then double click on it in order to replicate that fill color on WS2 and on WS3.
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim cellAdd As String, cellColor As Long
  cellAdd = Target.Address: cellColor = Target.Interior.ColorIndex: Cancel = True
  Sheets("WS2").Range(cellAdd).Interior.ColorIndex = cellColor
  Sheets("WS3").Range(cellAdd).Interior.ColorIndex = cellColor
End Sub
 
Solution

riksutin

New Member
Joined
Mar 30, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi! Thanks, that seems to work! :)

Is there a way to automatically run the code without double-clicking the cell? Now I have to double-click each of the cells and I have like 100 of them...
 

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
635
Office Version
  1. 365
Platform
  1. Windows
If you already have about 100 cells to replicate the fill color, then you can select as many of them as you want at a time and run the code below.
A copy of the code should be pasted in a standard module (Alt + F11 / Insert / Module).
VBA Code:
Sub ReplicateSelectionColor()
 Dim cellAdd As String, cellColor As Long, c As Range
  For Each c In Selection
   cellAdd = c.Address: cellColor = c.Interior.ColorIndex
   Sheets("WS2").Range(cellAdd).Interior.ColorIndex = cellColor
   Sheets("WS3").Range(cellAdd).Interior.ColorIndex = cellColor
  Next c
End Sub
 

riksutin

New Member
Joined
Mar 30, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you already have about 100 cells to replicate the fill color, then you can select as many of them as you want at a time and run the code below.
A copy of the code should be pasted in a standard module (Alt + F11 / Insert / Module).
VBA Code:
Sub ReplicateSelectionColor()
Dim cellAdd As String, cellColor As Long, c As Range
  For Each c In Selection
   cellAdd = c.Address: cellColor = c.Interior.ColorIndex
   Sheets("WS2").Range(cellAdd).Interior.ColorIndex = cellColor
   Sheets("WS3").Range(cellAdd).Interior.ColorIndex = cellColor
  Next c
End Sub
Thanks! So I have to manually run the code?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
There is no built-in way to check if the colour of a cell has been changed manually.
Therefore you can either run the code manually, via a double-click (or right-click) event, or via a selection change event. The latter would then run whenever a different cell is selected & could slow down the workbook
 

Watch MrExcel Video

Forum statistics

Threads
1,129,396
Messages
5,636,060
Members
416,896
Latest member
Hozier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top