I have written an UDF to count cells of certain color and with certain LineStyles, I'm posting the entire function:
Function CountTime(rData As Range, cellRefColor As Range) As Variant
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Variant
Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
cntRes = cntRes + 1
End If
If cellCurrent.Borders(xlDiagonalUp).LineStyle <> xlNone Then
cntRes = cntRes + 0.5
End If
Next cellCurrent
CountTime = cntRes
End Function
Now, the problem I have is that the formula does not automatically calculate when one of the cells in rData
has it's color or line properties changed.
I have added Application.Volatile
, and I also tried to trigger the calculation by a Worksheet_Change
sub, however that does not work as Excel does not seem to consider changing color a change to the cell/worksheet.
Is there any way to make the cell calculate and update automatically when a user changes the color or line properties of a cell in rData
?
EDIT -- SOLVED Thanks very much ignotus, the ChangeSelection workaround is good enough for my purposes. Didn't think of that. And the background info is also handy, thanks a lot.
I don't think it is possible, but a quite fair solution would be to recalculate when you leave a cell or change it:
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Calculate
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Calculate
End Sub
Or as an alternative, place a button on the sheet that calls Me.Calculate
FYI: What is Me
? Source
Me
refers to the parent object from which the code is "sitting" in. If you are writing in a Sheet module, the Me
will refer to that specific sheet.
Using Me
is handy because we don't have to worry about the sheet name changing, and it also makes it a little easy for future code readers as they don't have to remember that "Main UserForm" is the UserForm we are currently working on. You can apply the same methods to the Me
that you would be able to apply to the object if you gave the full name.
Within the Sheet1 module
, the following lines are identical in purpose:
Worksheets("Sheet1").Range("A1").Select
Me.Range("A1").Select