Cell Background Color Change --> Change Event

M

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
 
J

JLGWhiz

There is no Format change event.

mark said:
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
 
J

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.
 
S

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
 
M

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.
 
M

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top