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

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("D10:D24")
.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.


In article ,
"Larry" wrote:

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

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

"Larry" wrote:

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

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

-----Original Message-----
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
.

This did the trick. Thanks.
Larry

"JE McGimpsey" wrote:

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("D10:D24")
.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.


In article ,
"Larry" wrote:

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