Copy cell colors to the same cell in other worksheets

[riksutin]

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

 

[Osvaldo Palmeiro]

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.

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

 

[riksutin]

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]

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

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]

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

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]

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

 

[Osvaldo Palmeiro]

Thanks! So I have to manually run the code?

Yes. See the ways to do it.
How to Run a Macro in Excel - Automate Excel

 

[riksutin]

Thank you for all your help!