NoviceCoder
New Member
- Joined
- Apr 26, 2016
- Messages
- 20
I would be really grateful for any help on a problem that I've been struggling with for some time. I am making modification to an activity tracker which has time cells across columns (D:DP) and days of the week in rows (A14:A74). Users change the cell colours to plot activity and this is detected using a custom function to summarise daily and weekly activity. This works superbly thanks to help from this forum.
Function COUNTIFCOLOUR(Colour As Range, rng As Range) As Long
Application.Volatile (True)
Dim NoCells As Long
Dim CellColour As Long
Dim rngCell As Range
CellColour = Colour.Interior.Color
For Each rngCell In rng
If rngCell.Interior.Color = CellColour Then
NoCells = NoCells + 1
End If
Next
COUNTIFCOLOUR = NoCells
End Function
The sheet is updated by code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("D14:DQ74")) Is Nothing Then
Me.Calculate
End If
End Sub
*The problem I am having is that whenever I make changes to the sheet with VBA the summary cells (example is "=COUNTIFCOLOUR(DU15,D14:DP18)" fail to update and a green error triangle appears with hashtags. This can be resolved by going into the formula bar and pressing enter but this stops me automating the sheet in anyway. Even simple VBA code creates the same issue such as to delete a week:
Rows("14:20").Select
Selection.Delete Shift:=xlUp
Appplication.Calculate
Interestingly, if I perform the row deletion manually the UDF are okay.I've checked that calculations are set to automatic on preferences. When I run debug the toggle goes from the Delete Rows VBA code to module 1 code including the [COUNTIFCOLOUR Function] above and the formulae in summary cells error when the toggle reaches: CellColour = Colour.Interior.Color
I've even changed the Worksheet_SelectionChange to Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) in case it was sheet changes creating the error. It makes no difference.
I am completely stumped by this. I've spent hours troubleshooting. You're my last hope ; )
Function COUNTIFCOLOUR(Colour As Range, rng As Range) As Long
Application.Volatile (True)
Dim NoCells As Long
Dim CellColour As Long
Dim rngCell As Range
CellColour = Colour.Interior.Color
For Each rngCell In rng
If rngCell.Interior.Color = CellColour Then
NoCells = NoCells + 1
End If
Next
COUNTIFCOLOUR = NoCells
End Function
The sheet is updated by code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("D14:DQ74")) Is Nothing Then
Me.Calculate
End If
End Sub
*The problem I am having is that whenever I make changes to the sheet with VBA the summary cells (example is "=COUNTIFCOLOUR(DU15,D14:DP18)" fail to update and a green error triangle appears with hashtags. This can be resolved by going into the formula bar and pressing enter but this stops me automating the sheet in anyway. Even simple VBA code creates the same issue such as to delete a week:
Rows("14:20").Select
Selection.Delete Shift:=xlUp
Appplication.Calculate
Interestingly, if I perform the row deletion manually the UDF are okay.I've checked that calculations are set to automatic on preferences. When I run debug the toggle goes from the Delete Rows VBA code to module 1 code including the [COUNTIFCOLOUR Function] above and the formulae in summary cells error when the toggle reaches: CellColour = Colour.Interior.Color
I've even changed the Worksheet_SelectionChange to Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) in case it was sheet changes creating the error. It makes no difference.
I am completely stumped by this. I've spent hours troubleshooting. You're my last hope ; )
