Can a UDF be used on an array of cells?

[Paul D. Simon]

I've been using this "IsRed" UDF for some time now to identify cells in
which data has been changed to red.

Public Function IsRed(rg As Range) As Boolean
Application.Volatile
IsRed = rg.Font.ColorIndex = 3
End Function

For example, in cell B1, I would enter this formula to test cell A1:

=if(IsRed(A1),1,0)

I have 2 questions regarding this:

1.) If I change the entry in cell A1 to red, B1 will automatically
change to 1. Conversely, however, if I change A1 back to black (or any
other color), B1 does not automatically change to 0. I have to hit F9
in order for that to happen, and if I (or the user) forgets to hit F9,
we've got problems. Is there any way to have it automatically change
to 0 just like it automatically changes to 1?

2.) In a particular worksheet, there are about 500 rows of data in 33
fields that go from column A through column AG. The user will indicate
changes he makes to data by changing the color of that data to red, and
I need to test for any record where the contents of any cells have been
changed to red. I could enter 33 corresponding if(IsRed) formulas in
cells AJ through BP, but at 33 such formulas per record times 500
records, that's a lot of formulas! Is there a way to use if(IsRed) on
an array of cells? The following obviously doesn't work, but it
illustrates what I'm trying to accomplish: in cell AJ2
=if(IsRed(A2:AG2),1,0)

Many thanks,
Paul

 

[Guest]

Here is how you can get your UDF to work on a range:

Public Function IsRed(rg As Range) As Boolean
Dim temp As Boolean
For Each rg In rg
Application.Volatile
temp = rg.Font.ColorIndex = 3
If temp Then
IsRed = True
End If
Next
End Function

 

[Paul D. Simon]

David,

Absolutely perfect! Exactly what I was looking for.

Thank you very much!
Paul

 

[Guest]

Hi Paul,

Option Explicit

Public Function AllIsRed(rng As Range) As Boolean
Dim rngcell As Range
Application.Volatile
AllIsRed = True
For Each rngcell In rng
If rngcell.Font.ColorIndex <> 3 Then
AllIsRed = False
Exit Function
End If
Next
End Function

Public Function AnyIsRed(rng As Range) As Boolean
Dim rngcell As Range
Application.Volatile
AnyIsRed = False
For Each rngcell In rng
If rngcell.Font.ColorIndex = 3 Then
AnyIsRed = True
Exit Function
End If
Next
End Function


HTH,
Bernd