Cell Background Color Change --> Change Event

[mark]

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

 

[JLGWhiz]

There is no Format change event.

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

 

[JLGWhiz]

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.

 

[Shane Devenshire]

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]

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.

 

[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)