Change the background of a cell based on another cell's background

[simona123]

Can somebody help me to change the background color of a cell depending on the color of another cell. I tried this:

Range("A1").Interior.Color = Range("A1").Interior.Color

It works. But my question is whether it can be done automatically? What i mean , is that every time I change the color in B1 to change it in A1 also. I do not normally work with VBA, that's why I'm asking.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Fill B1 with a color and exit the cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Colr
    Colr = Range("B1").Interior.Color
    Range("A1").Interior.Color = Colr
End Sub

 

[JLGWhiz]

So long as your colors are set by interior fill rather than conditional format, you can copy the colors to other cells. You need to be more specific about how you want the colors to automatically work. If you are only working with columns A and B then this would automatically copy the adjacent cell color if a cell in A or B columns is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 1 Then
    Target.Interior.Color = Target.Offset(, 1).Interior.Color
ElseIf Target.Column = 2 Then
    Target.Interior.Color = Target.Offset(, -1).Interior.Color
End If
End Sub

This code should be copied to the Worksheet code module. It will run when changes are made to the sheet.