Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can a UDF be used on an array of cells?
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 |
#2
|
|||
|
|||
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 -- Regards, Dave "Paul D. Simon" wrote: 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 |
#3
|
|||
|
|||
David,
Absolutely perfect! Exactly what I was looking for. Thank you very much! Paul |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Order of selecting unprotected cells | Excel Discussion (Misc queries) | |||
Searching for a criteria in array of cells within an IF statement | Excel Worksheet Functions | |||
how do you "select locked cells" w/o "select unlocked cells"? | Excel Discussion (Misc queries) | |||
Blank Cells in Pivot Tables | Excel Discussion (Misc queries) | |||
Can I count how many grey-filled cells are in a row ? | Excel Worksheet Functions |