Help with User Defined Function

MDonohue

New Member
Joined
Sep 22, 2014
Messages
3
Hi all,

First, I want to say thank you for all the help you have unknowingly provided me in the past with Excel. You guys are a wealth of knowledge.

I have written the below UDF to search through a target range and return the value of the first cell that has any interior color (with a couple specific exceptions) and the value of the cell is greater than the cell above it in the list. All of the values are dates and are sequential and are separated by month in a standard calendar format (i.e. each month has it's own block or group of cells - January is A8 to H13, February is J8 to P13, and so on). The first date in the list is not determined through this function. I've run into a few issues that I am not sure how to work around.

The first is issue is that when the workbook is manually calculated using CTRL+ALT+F9, only the first cell that contains this formula will display the correct date. The rest are just duplicates of that same date. I have to manually click into each cell's formula and tab or enter out of it for it to update correctly. Is there a way to get them to populate correctly with the manual calculation?

The second issue is that after a manual calculation and updating the cells manually, it will skip dates at random. For instance, I can have 2/11/14, 2/27/14, 3/12/14, 5/13/14, and 5/20/14 colored but it will skip over 2/27/14. Sometimes more than one is excluded, even though the coloring and cell value criteria fit.

Any help would be much appreciated!

Code:
Function ColorDate2(rRange As Range)

If ActiveCell.Offset(-1, 0).Value = 0 Then
ActiveCell.Value = 0

Else

    Dim rCell As Range
    Dim vResult

For Each rCell In rRange
If rCell.Interior.ColorIndex >= 0 And rCell.Value > ActiveCell.Offset(-1, 0).Value And rCell.Interior.Color <> RGB(86, 108, 128) And rCell.Interior.Color <> RGB(157, 174, 189) Then
vResult = rCell.Value
End If
If vResult > 0 Then
Exit For
End If
Next rCell
ColorDate2 = vResult
End If

End Function
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Your use of ActiveCell concerns me.
ActiveCell is the cell that is active when the UDF is run.
The cell that contains the formula is refered to with Application.Caller, but by your description, that's not what you are looking at.
Perhaps
Code:
For Each rCell In rRange
    With rCell
        If .Interior.ColorIndex >= 0 And .Value > .Offset(-1, 0).Value And .Interior.Color <> RGB(86, 108, 128) And .Interior.Color <> RGB(157, 174, 189) Then
            vResult = .Value
        End If
        If vResult > 0 Then Exit For
    End With
Next rCell
 
Upvote 0
Thanks for the quick reply.

The ActiveCell was a good catch. I've replaced the beginning portion with Application.Caller and that solved the duplicating data issue. Although it did cause a circular reference error to occur in one of the bottom cells of the list. I can worry about that later though.

I used a direct copy of yours but noticed one of the criteria didn't fit. The Offset(-1,0).Value needs to be based off the cell running the formula since the cell above that would have the previous date that was colored.

Slight change to include the criteria but still skipping dates at what seems to be random:

Code:
For Each rCell In rRange
    With rCell
        If .Interior.ColorIndex >= 0 And .Value >  Application.Caller.Offset(-1, 0).Value And .Interior.Color <>  RGB(86, 108, 128) And .Interior.Color <> RGB(157, 174, 189) Then
            vResult = .Value
        End If
        If vResult > 0 Then Exit For
    End With
Next rCell
 
Upvote 0
If you post some sample data and the expected result it would help us understand what is supposed to be compared to what.
I am not a fan of UDF's that hard code cell references, relative or absolute, perhaps you could use two arguments.
rRange, the range of cells to be checked for color and value
referenceValue, the reference valut that cells in rRange is to be compared to.

Neither am I a fan of UDF's that depend on a cell's color. Changing the color of a cell does not trigger calculation, neither does it trigger an event. Excel is designed to receive input from cell values. Cell color is designed as part of its output suite.
 
Upvote 0
I'm not fond of using the cell color as the criteria either. But points are being assigned to different days based on different events that occur and cell color seems to be the only way to show that on a calendar type view, with a legend for the colors.

Below is how the months are setup (true to the calendar year for each month) and the area that holds the UDF. The UDF area is off to the side of the full 12 months in the actual spreadsheet, not under each month. The rRange of the UDF is set to encompass all days of all months.

SMTWTFS
01020304
05060708091011
12131415161718
19202122232425
262728293031

<tbody>
</tbody>

DateEventPoints
First Colored Date
"ColorFunction2"
"ColorFunction2"
"ColorFunction2"

<tbody>
</tbody>

I feel like there is a better way to show some sample data... Is there a way to attach a file to the reply? The issue only occurs when there are multiple months of data that have colored cells in them and that would be a lot of rows and columns to build into here. If necessary, I will take that route though.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top