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!
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