How do I get multiple cells automatically change color/copy a different cell

[Paul365]

Hello,

I am trying to have many cells on the same worksheet copy the cells color. Let's say I change cell 'A1' to green (or any color), I would like Cell 'A30' 'A50' 'A66' etc. to automatically change to the same color.

Can this be done? I know how to have the cell show the same text by using the formula =A1, howver I would like the boxes to show the same color too.

Any help would be great, I have looked into VBA and conditional formatting.

 

[rollis13]

There is no event to detect (that triggers with) a change of color in a cell so try this workaround. Note that the other cells will color only when you move from cell A1 after changing it's color.
As per your example in a text workbook in ThisWorkBook's module paste this macro:

Option Explicit
Private Sub Workbook_Open()
    Range("B1").Select                            '<- as range choose an unused cell but not A1
End Sub

and in the sheet's module paste this:

Option Explicit
Public LastRange As Range
Public LastCellColor As Integer
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If LastCellColor = 0 Then GoTo setvar
    If LastRange.Interior.ColorIndex <> LastCellColor Then
        If Not Intersect(LastRange, Range("A1")) Is Nothing Then
            Union(Range("A30"), Range("A50"), Range("A66")).Interior.ColorIndex = LastRange.Interior.ColorIndex
        End If
    End If
setvar:
    Set LastRange = Target
    LastCellColor = Target.Interior.ColorIndex
End Sub

If the test goes well, try integrating everything into your project.