Hi,

I have a whole bunch of cells which are formatted differently based on
a master set of cells. What I'm trying to do is, when the user changes
the colour of any of the cells in the master set, the other cells with
the previous same formatting should accordingly. Unfortunately, it
doesn't seem like formatting changes trigger a recalculation.

My code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim k As Integer
If Not (Target.MergeCells And IsInRange(Target,
Range("colorLegend"))) Then
Exit Sub
End If
If legendMap Is Nothing Then
genLegendMap
End If
For k = 0 To (legendMap.Count - 1)
legendMap.Items(k).Interior.Color =
legendMap.Keys(k).Interior.Color
Next k
End Sub

It's pretty silly considering that the colour of the cells only
changes if another cell is selected. Does anyone have a better way of
achieving what I mentioned at the top of my post?

There is no formatting event that you can trap. You would have to build a
facility that is used to invoke the colour change, and do the others at the
same time.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"midori" <shuying@gmail.com> wrote in message
news:d10528a3-90ef-4813-9659-ffbe3a1a255a@p25g2000pri.googlegroups.com...
> Hi,
>
> I have a whole bunch of cells which are formatted differently based on
> a master set of cells. What I'm trying to do is, when the user changes
> the colour of any of the cells in the master set, the other cells with
> the previous same formatting should accordingly. Unfortunately, it
> doesn't seem like formatting changes trigger a recalculation.
>
> My code:
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim k As Integer
> If Not (Target.MergeCells And IsInRange(Target,
> Range("colorLegend"))) Then
> Exit Sub
> End If
> If legendMap Is Nothing Then
> genLegendMap
> End If
> For k = 0 To (legendMap.Count - 1)
> legendMap.Items(k).Interior.Color =
> legendMap.Keys(k).Interior.Color
> Next k
> End Sub
>
> It's pretty silly considering that the colour of the cells only
> changes if another cell is selected. Does anyone have a better way of
> achieving what I mentioned at the top of my post?