One cell's formatting mirrors another

[dgoldman86]

In cell A1 i have a drop down list with conditional formatting in place. Depending on the value i select, the cell A1 will change fill color. I want cell B1 to change fill color to match the fill color in A1.

Simple example: say i select value 1 for cell A1 and the fill color in A1 goes to blue. I would like cell B1 to turn blue also. Then if i change the value of A1 to 2 and A1 fill color changes green, I want B1 to also change to green.

Any help is greatly appreciated.

 

[storm8]

you need worksheet event. Place following code into the appropriate sheet module under MS excel Objects (not in normal module)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Range("B1").Interior.Color = Target.Interior.Color
    End If
End Sub

 

[Snakehips]

dgoldman86,

Welcome rot MrExcel.

Whatever CF rules you have in place for cell A1, extend the 'Applies to' range in the CF Rules Manager, to apply to B1.

Eg if current range = $A$1 then make $A$1:$B$1
Also ensure that the rule formula for A has appropriate absolute references ($ where necessary)

Hope that helps.

 

[Snakehips]

you need worksheet event. Place following code into the appropriate sheet module under MS excel Objects (not in normal module)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Range("B1").Interior.Color = Target.Interior.Color
    End If
End Sub

storm8,
The above will make B1 interior the same colour as the 'base' interior colour of Target, not the colour it is displaying as a result of any conditional format.

The CF colour is not impossible to get hold of but it is not easy.

Ref [URL]http://www.excelfox.com/forum/f22/get-displayed-cell-color-whether-from-conditional-formatting-or-not-338/index2.html

[/URL]By Rick Rothstein

 

[storm8]

Are you sure Snekehips? I dont think you can change CF based on other cells value, at least not easily and not always.
You can have B1 show what A1 shows and then apply same CF on both, but if you want B2 to stay empty you have to use VBA. Am I wrong?

But you're right, mine solution copy the fixed format, not CF. Whoops

 

[dgoldman86]

dgoldman86,

Welcome rot MrExcel.

Whatever CF rules you have in place for cell A1, extend the 'Applies to' range in the CF Rules Manager, to apply to B1.

Eg if current range = $A$1 then make $A$1:$B$1
Also ensure that the rule formula for A has appropriate absolute references ($ where necessary)

Hope that helps.

This doesn't work either because the value in B1 does not satisfy the criteria for the conditional formatting in A1.

 

[storm8]

I suggest you abandon CF and use VBA and some nested if or select case to get desired format, then update B1 likewise

 

[dgoldman86]

I suggest you abandon CF and use VBA and some nested if or select case to get desired format, then update B1 likewise

Alright thanks for the help, unfortunately i think that is beyond my skill level. I am definitely a novice and can only write the most basic macros.

 

[storm8]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
            
        Dim clr
            
        Select Case Target.Value
            Case 1: clr = 255
            Case 2: clr = 100
            Case 3: clr = 400
        End Select
            
        Range("A1").Resize(1, 2).Interior.Color = clr
    End If
End Sub

 

[Snakehips]

Here are two examples.........

For A1:B6 The CF formula does not refer to the value of B. It is about A equal to sum of C & D
The refers to range is $A1:$B6

Sheet1

*	A	B	C	D
*

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 107px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ff0000, align: center"]9[/TD]
[TD="bgcolor: #ff0000, align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: center"]99[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #ff0000, align: center"]8[/TD]
[TD="bgcolor: #ff0000, align: center"]999[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]WWW[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #ff0000, align: center"]23[/TD]
[TD="bgcolor: #ff0000, align: center"]XXX[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]01/01/2014[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]5[/TD]

</TBODY>

Conditional formatting

Cell Nr.: / Condition Format A1 1. / Formula is =$A1=$C1+$D1 <TBODY> [TD="bgcolor: #ff0000"]Abc

<TBODY>

</TBODY>

[/TD]

</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

For A8;B10 The CF formula does not refer to the value of B nor A. It is about C & D .
The refers to range is $A8:$B10
Sheet1

*	A	B	C	D

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 107px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #92d050, align: center"]99[/TD]
[TD="bgcolor: #92d050, align: right"]999[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #92d050, align: center"]999[/TD]
[TD="bgcolor: #92d050, align: right"]99[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]99[/TD]
[TD="align: right"]999[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7[/TD]

</TBODY>

Conditional formatting

Cell Nr.: / Condition Format A8 1. / Formula is =$C8+$D8 = 9 <TBODY> [TD="bgcolor: #92d050"]Abc

<TBODY>

</TBODY>

[/TD]

</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


In both cases the colour of B mirrors A, irrespective of value in B.

Hope that helps.