Set the color of one cell based on the color of another cell

Question

What I would like to have is:

IF   A1 in Sheet 2 is blue  
Then A1 in Sheet 1 changes to blue

I know I can get the color of A1 in Sheet 2 by using:

=GET.CELL(63,Sheet2!A1)

(Excel: Can I create a Conditional Formula based on the Color of a Cell?)

But I can't figure out what I should do in the next step.

Update on 12.01.2015

At the beginning I thought a function would work, but as I considered my file, VBA may be needed.

It is about the output of a correlation analyse from SPSS, there are three columns: correlation coefficient, p-value and sample size. I need to check the coefficient and p-value at the same time, and present the coefficient in a readable way. Say I run a correlation between 50 variables with 100 variables, I would not paste coefficient and p-value in one sheet, rather:

sheet one : coefficient sheet two: p-value

What I would to have is:

If value of p-value is bigger than 0.05, then coefficient (cell) changes to blue/dark blue or black.

So that when I watch the first sheet, I know blue ones should be ignored because of non-significance.

Answer 1

What you need is a way to detect changes in cell format. There appears to be no event that triggers upon change in format. See How to detect changes in cell format?

I will describe a workaround, almost step-by-step. It is not keystroke-by-keystroke, so you may have to google a bit, depending on you background knowledge. The description is not short, so please read it through.

You have to:

1. Detect change in Selection (there is an event for this).
2. Inquire about the color of your source cell.
3. Act if needed.

Go to the Visual Basic Editor (VBE) and add code in three modules:

1. A standard module (say, Module1). You have to first insert the module.
2. ThisWorkbook.
3. Sheet2.

In Module1:

Public prev_sel As Range
Public wssrc As Worksheet, wstrg As Worksheet
Public ssrc As String, strg As String
Public rngsrc As Range, rngtrg As Range

Sub copy_color(rngs As Range, rngt As Range)
    Dim csrc As Long
    csrc = rngs.Interior.Color
    If (csrc = vbBlue) Then
        rngt.Interior.Color = vbBlue
    End If
End Sub

Sub copy_color2(rngs As Range, rngt As Range)
    If (TypeName(prev_sel) = "Range") Then
        Dim pss As String
        pss = prev_sel.Parent.Name
        If (pss = ssrc) Then
            Dim ints As Range
            Set ints = Application.Intersect(rngs, prev_sel)
            If (Not (ints Is Nothing)) Then
                Call copy_color(rngs, rngt)
            End If
        End If
    End If
End Sub

In ThisWorkbook:

Private Sub Workbook_Open()
    ssrc = "Sheet2"
    strg = "Sheet1"
    Set wssrc = Worksheets(ssrc)
    Set wstrg = Worksheets(strg)
    Set rngsrc = wssrc.Range("A1")
    Set rngtrg = wstrg.Range("A1")
    Call copy_color(rngsrc, rngtrg)

    If (TypeName(Selection) = "Range") Then
        Set prev_sel = Selection
    Else
        Set prev_sel = Nothing
    End If
End Sub

In Sheet2:

Private Sub Worksheet_Deactivate()
    Call copy_color(rngsrc, rngtrg)
    If (TypeName(Selection) = "Range") Then
        Set prev_sel = Selection
    Else
        Set prev_sel = Nothing
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call copy_color2(rngsrc, rngtrg)
    If (TypeName(Target) = "Range") Then
        Set prev_sel = Target
    End If
End Sub

I will soon edit with explanations. Reading carefully, it can be readily understood, though.

Notes:

1. This code does not act if the source cell color changes from vbBlue to something else. You did not specify anything for this action. Actually, your specification was not detailed enough to cover all possible cases.

2. There might be cases (extremely unlikely, I guess) where this code fails. For instance, if color is changed via other VBA code, without selecting/deselecting cells.

3. The idea is to check for the need of acting after as many relevant events as possible. Here I am detecting Workbook_Open, Worksheet_Deactivate, Worksheet_SelectionChange. You may add other events with suitable Subs, e.g., Workbook_BeforeClose, Workbook_BeforeSave. All this is a way of substituting for the non-existing event of changing cell format.

4. I like the answer by pnuts (although I did not have time to test it). But the present one gives a flexibility that is not available with the other. There might be some cases (depending on what you need to do) that would not be covered by it.

5. There are other possible combinations of places to locate variables declaration and other code, essentially performing the same actions.