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