TesseractE
New Member
- Joined
- Nov 30, 2011
- Messages
- 38
I'm trying to put together a method for letting people tally up short, medium and long calls and to document the date along with those tallies. So far, I've got some code that will change the background color and leave the current date if the cell is double-clicked.
The first double-click turns it green to signify a short call, Another double-click shows yellow for a medium length call, another shows red for a long call, then a fourth will clear the cell.
To go along with this, I have a UDF to count the number of times each color shows up.
It all seems to work just fine...
...but if one of the cells cycles back to blank, I get #VALUE! errors from the COUNTCOLOR function.
Refreshing the function by editing, then hitting enter (or Tab) will bring up the correct value again.
Any suggestions on how to patch this up to be a little more reliable?
The first double-click turns it green to signify a short call, Another double-click shows yellow for a medium length call, another shows red for a long call, then a fourth will clear the cell.
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Interior.ColorIndex = 3 Then
Target.Interior.ColorIndex = xlNone
Target.Cells.Clear
Else
If Target.Interior.ColorIndex = 6 Then
Target.Interior.ColorIndex = 3
Target.Cells.Value = Format(Date, "mmm dd")
Else
If Target.Interior.ColorIndex = 4 Then
Target.Interior.ColorIndex = 6
Target.Cells.Value = Format(Date, "mmm dd")
Else
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 4
Target.Cells.Value = Format(Date, "mmm dd")
End If
End If
End If
End If
Cancel = True
End Sub
To go along with this, I have a UDF to count the number of times each color shows up.
Code:
Function COUNTCOLOR(R As Range, C As Integer)
Dim cell As Object
Dim N As Integer
For Each cell In R
If cell.Interior.ColorIndex = C Then
N = N + 1
End If
Next
COUNTCOLOR = N
End Function
It all seems to work just fine...
...but if one of the cells cycles back to blank, I get #VALUE! errors from the COUNTCOLOR function.
Refreshing the function by editing, then hitting enter (or Tab) will bring up the correct value again.
Any suggestions on how to patch this up to be a little more reliable?