Need help troubleshooting code which changes the color of a cell

[Guest]

I have a spreadsheet with text in one column and a category in the next. I
want to count the uncategorized items, then highlight the cell so it's easy
to find. The following code works if I execute the code from the vb window
(the value is calculated correctly and blank cells are highlighted). If I
recalculate this in the spreadsheet, the value of the cell is updated to the
correct number, but the cell colors are not changed. If I set a breakpoint
within the if statement, then do a recalculate in the spreadsheet, the
breakpoint is hit. If I step through the code the color is changed. Any
ideas to help me troubleshoot this?

Function CountUncategorized()
Count = 0
For RowCount = 10 To 24
Cells(RowCount, 4).Interior.ColorIndex = 0
If (Cells(RowCount, 3) <> "" And Cells(RowCount, 4) = "") Then
Count = Count + 1
Cells(RowCount, 4).Interior.ColorIndex = 3
End If
Next RowCount
CountUncategorized = Count
End Function

 

[JE McGimpsey]

Worksheet functions, including UDFs called from the worksheet, can only
return values to their calling cells. They can't change environment
settings, like font or background colors.

You can instead use a Worksheet Event macro. Put this in your worksheet
code module (right-click on the worksheet tab and choose View code):

Private Sub Worksheet_Calculate()
Dim rCell As Range
Dim nCount As Long
With Range("D1024")
.Interior.ColorIndex = xlColorIndexNone
For Each rCell In .Cells
With rCell
If Not IsEmpty(.Offset(0, -1).Value) And _
IsEmpty(.Value) Then
nCount = nCount + 1
.Interior.ColorIndex = 3
End If
End With
Next rCell
End With
Application.EnableEvents = False
Range("A1").Value = nCount
Application.EnableEvents = True
End Sub


Change the "A1" reference at the end to the cell you want your result in.

 

[Guest]

Larry,
One thought, perhaps pass the worksheet object to your function, so you know
the code is working against what you want it to work against. Your example
below may not be.

Function intCountUncategorized(wksData as Worksheet) as Integer
Dim intRow as Integer

For intRow = 10 To 24
wksData.Cells(intRow, 4).Interior.ColorIndex = xlColorIndexNone
If (wksData.Cells(intRow, 3) <> "" And wksData.Cells(intRow, 4) =
"") Then
intCountUncategorized= intCountUncategorized + 1
wksData.Cells(intRow, 4).Interior.ColorIndex = 3
End If
Next intRow
End Function

Hope it helps,
Dale Preuss

 

[d]

Try using explicit call to the objects -excel likes that
especially if working w more than one worksheet -- hope
this helps.

 

[Guest]

This did the trick. Thanks.
Larry

Worksheet functions, including UDFs called from the worksheet, can only
return values to their calling cells. They can't change environment
settings, like font or background colors.

You can instead use a Worksheet Event macro. Put this in your worksheet
code module (right-click on the worksheet tab and choose View code):

Private Sub Worksheet_Calculate()
Dim rCell As Range
Dim nCount As Long
With Range("D1024")
.Interior.ColorIndex = xlColorIndexNone
For Each rCell In .Cells
With rCell
If Not IsEmpty(.Offset(0, -1).Value) And _
IsEmpty(.Value) Then
nCount = nCount + 1
.Interior.ColorIndex = 3
End If
End With
Next rCell
End With
Application.EnableEvents = False
Range("A1").Value = nCount
Application.EnableEvents = True
End Sub


Change the "A1" reference at the end to the cell you want your result in.