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

Not recommended because of reliance on the XLM (not XML) Macro function GET.CELL. This from a technology introduced 30 years ago that was effectively superseded eight years later. With almost all its elements now defunct, the few that remain can be expected to have a low life expectancy. Microsoft encourages migration to VBA.

Nevertheless, you have asked ‘how’ rather than ‘why not’, so I suggest you proceed from where you have reached and select Sheet1 A1 and HOME > Styles - Conditional Formatting - New Rule..., Use a formula to determine which cells to format, Format values where this formula is true:

=CellColor=23  

and select blue formatting of your choice, OK, OK, Apply.

23 is a fairly standard number for Blue (not Light, not Dark) but your configuration may expect a different number.

Note that another disadvantage is that, unlike CF in general, the response is not automatic – you may need to enter something in Sheet1 A1, or Shift+F9 to force an update.

---

If your data is spread across two sheets (Sheet1 and Sheet2, both ColumnA) and there is a 1:1 relationship (the p-value in A1 of Sheet2 is that for the correlation coefficient in A1 of Sheet1) then a simple Conditional Formatting rule may suffice:

Select Sheet1 ColumnA and HOME > Styles - Conditional Formatting, New Rule...

Use a formula to determine which cells to format
Format values where this formula is true:

=Sheet2!A1>0.05

Format..., select dark blue or to suit, OK, OK.

The same rule might be applied in Sheet2 (ColumnA) in the same way so the cells (by row) conditionally formatted in one sheet are those conditionally formatted in the other.

Answer 2

The GET.CELL function, whilst useful, comes from the old XLM macro language which was used before VBA. You may encounter restrictions using this e.g. at that time Excel used a limited number of colours (I have read somewhere around 60?).

Alternatively, with a bit of VBA, you could experiment with the Interior Object and also the Font Object :

Sheets("Sheet1").Range("A1").Interior.Color = vbBlue
Sheets("Sheet1").Range("A1").Font.Color = vbYellow
    If Sheets("Sheet1").Range("A1").Interior.Color = vbBlue Then _
    Sheets("Sheet2").Range("A1").Interior.Color = vbBlue
    If Sheets("Sheet1").Range("A1").Font.Color = vbYellow Then _
    Sheets("Sheet2").Range("A1").Font.Color = vbYellow

You will likely need to explore the various ways of specifying the colors to use to give you maximum control/flexibility.

Answer 3

Just to be clear and to keep the functionality you deliver simple, you could use conditional formatting and choose to set the format with a colour. This is incredibly easy once you know how. The main trick is what formula to enter and specifically which cell you need a conditional formats formula to reference when the conditional format applies to a multi cell range.

As an example. If your conditional formatting rule is created such that it applies to the range $C$5:$C$10 the formula you use will often need to be entered as =(A5="A"). Note this is a relative addressing formula ie no dollar signs. this has the effect of the cell c6 inspecting the value of a6 etc.

Your only complication now is to inspect the formatting of the cell rather than the value it stores. In 2013, you can still use =GET.CELL(63,A5) to do this, however this can't be entered in the formula of the CF rule ... Other posts discuss the whys and wherefores of using this. See this link which described how to get cell info.

So you'll end up with a formula in a cell next to the cell that has the colouring. The formula will use a named range that returns true or false depending on whether the colour of the cell matches the colour you specify in the named range. You conditional formatting on another sheet will reference this formula cell and set the colour of the new cell.

You would use the following formula in the named range called "Get . =GET.CELL(65,OFFSET(INDIRECT("RC",FALSE),0,1))

I've got this to work, and the key information can be found one the referenced web site page.

Ok?

Answer 4

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.