Hello.

A user wants to sum value based upon cell color background.

I gave her a custom function to do thatm, based upon this:
>>>>>>>>>
Function CellColorIndex(InRange As Range) As Integer

' This function returns the ColorIndex value of the Interior (background) of
a cell

Application.Volatile True

CellColorIndex = InRange.Interior.ColorIndex

End Function
>>>>>>>>>>>

But, when she changes the cell color, this function is not recalculating,
unless she specifically tells it to.

I tried putting an Activesheet.usedrange.calculate into the worksheet's
change event, but it appears that a change in a cell's background color
format, does not fire the worksheet change event.

Can someone suggest a good way to make this dynamic, based upon the current
cell color?

Thanks.
Mark

There is no Format change event.

"mark" wrote:

> Hello.
>
> A user wants to sum value based upon cell color background.
>
> I gave her a custom function to do thatm, based upon this:
> >>>>>>>>>
> Function CellColorIndex(InRange As Range) As Integer
>
> ' This function returns the ColorIndex value of the Interior (background) of
> a cell
>
> Application.Volatile True
>
> CellColorIndex = InRange.Interior.ColorIndex
>
> End Function
> >>>>>>>>>>>
>
> But, when she changes the cell color, this function is not recalculating,
> unless she specifically tells it to.
>
> I tried putting an Activesheet.usedrange.calculate into the worksheet's
> change event, but it appears that a change in a cell's background color
> format, does not fire the worksheet change event.
>
> Can someone suggest a good way to make this dynamic, based upon the current
> cell color?
>
> Thanks.
> Mark
>

Sorry to cut that so short. Had an emergency.
Like I said, there is no Format change event, but you might use the
worksheet_selectionchange event to trigger the macro.

"mark" wrote:

> Hello.
>
> A user wants to sum value based upon cell color background.
>
> I gave her a custom function to do thatm, based upon this:
> >>>>>>>>>
> Function CellColorIndex(InRange As Range) As Integer
>
> ' This function returns the ColorIndex value of the Interior (background) of
> a cell
>
> Application.Volatile True
>
> CellColorIndex = InRange.Interior.ColorIndex
>
> End Function
> >>>>>>>>>>>
>
> But, when she changes the cell color, this function is not recalculating,
> unless she specifically tells it to.
>
> I tried putting an Activesheet.usedrange.calculate into the worksheet's
> change event, but it appears that a change in a cell's background color
> format, does not fire the worksheet change event.
>
> Can someone suggest a good way to make this dynamic, based upon the current
> cell color?
>
> Thanks.
> Mark
>

Hi,

probably you should trigger the Worksheets(1).Calculate command inside an
Change event:

Here is some sample code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
'Your code here
End If
End Sub

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"mark" <(E-Mail Removed)> wrote in message
news:9D4293C6-7842-46AF-9D39-(E-Mail Removed)...
> Hello.
>
> A user wants to sum value based upon cell color background.
>
> I gave her a custom function to do thatm, based upon this:
>>>>>>>>>>
> Function CellColorIndex(InRange As Range) As Integer
>
> ' This function returns the ColorIndex value of the Interior (background)
> of
> a cell
>
> Application.Volatile True
>
> CellColorIndex = InRange.Interior.ColorIndex
>
> End Function
>>>>>>>>>>>>
>
> But, when she changes the cell color, this function is not recalculating,
> unless she specifically tells it to.
>
> I tried putting an Activesheet.usedrange.calculate into the worksheet's
> change event, but it appears that a change in a cell's background color
> format, does not fire the worksheet change event.
>
> Can someone suggest a good way to make this dynamic, based upon the
> current
> cell color?
>
> Thanks.
> Mark
>

but, like I said, and JLGWhiz confirmed, changing the background color
doesn't fire the worksheet_change event.

so, placing a worksheets(1).calculate command inside a worksheet_change
event, isn't going to do anything.

It sounds like JLGWhiz's suggestion to cause it to calculate upon a
selection change, is the closest that's possible.

"Shane Devenshire" wrote:

> Hi,
>
> probably you should trigger the Worksheets(1).Calculate command inside an
> Change event:
>
> Here is some sample code:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim isect As Range
> Set isect = Application.Intersect(Target, Range("A1"))
> If Not isect Is Nothing Then
> 'Your code here
> End If
> End Sub
>
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
> "mark" <(E-Mail Removed)> wrote in message
> news:9D4293C6-7842-46AF-9D39-(E-Mail Removed)...
> > Hello.
> >
> > A user wants to sum value based upon cell color background.
> >
> > I gave her a custom function to do thatm, based upon this:
> >>>>>>>>>>
> > Function CellColorIndex(InRange As Range) As Integer
> >
> > ' This function returns the ColorIndex value of the Interior (background)
> > of
> > a cell
> >
> > Application.Volatile True
> >
> > CellColorIndex = InRange.Interior.ColorIndex
> >
> > End Function
> >>>>>>>>>>>>
> >
> > But, when she changes the cell color, this function is not recalculating,
> > unless she specifically tells it to.
> >
> > I tried putting an Activesheet.usedrange.calculate into the worksheet's
> > change event, but it appears that a change in a cell's background color
> > format, does not fire the worksheet change event.
> >
> > Can someone suggest a good way to make this dynamic, based upon the
> > current
> > cell color?
> >
> > Thanks.
> > Mark
> >
>

Thanks for the suggestion... too bad this isn't more directly available.

Lotus 5.0 had an internal function that woud read cell color. (I know Lotus
5.0 was pre Win95, and way out of date now, but it did have some nice stuff
for it's time)

"JLGWhiz" wrote:

> Sorry to cut that so short. Had an emergency.
> Like I said, there is no Format change event, but you might use the
> worksheet_selectionchange event to trigger the macro.
>
> "mark" wrote:
>
> > Hello.
> >
> > A user wants to sum value based upon cell color background.
> >
> > I gave her a custom function to do thatm, based upon this:
> > >>>>>>>>>
> > Function CellColorIndex(InRange As Range) As Integer
> >
> > ' This function returns the ColorIndex value of the Interior (background) of
> > a cell
> >
> > Application.Volatile True
> >
> > CellColorIndex = InRange.Interior.ColorIndex
> >
> > End Function
> > >>>>>>>>>>>
> >
> > But, when she changes the cell color, this function is not recalculating,
> > unless she specifically tells it to.
> >
> > I tried putting an Activesheet.usedrange.calculate into the worksheet's
> > change event, but it appears that a change in a cell's background color
> > format, does not fire the worksheet change event.
> >
> > Can someone suggest a good way to make this dynamic, based upon the current
> > cell color?
> >
> > Thanks.
> > Mark
> >