Combining Two Functions

Thank you very much Mr. Deitrick.

This works perfectly.

Well,
Almost perfectly.
The results are not persistent. If I navigate to another worksheet, when I come back it is displaying the #Value error. Same thing when I close the workbook and reopen it.

Well,
Almost perfectly.
The results are not persistent. If I navigate to another worksheet, when I come back it is displaying the #Value error. Same thing when I close the workbook and reopen it.

If I go to the cell with the =CheckColor(A1) entry and pretend edit, it comes back. Hitting the Calculate Now button on the formulas ribbon doesn't do anything either.

Thanks again for your time Mr. Deitrick.

Application.Volatile didn't help.

I do have a Worksheet_Activate() procedure and I've tried various things to call out the function from within it but haven't had any luck. Everything I try complies with errors. Can a function be called out by a procedure?

Here's my Worksheet_Activate() code (with tags this time). As you can see, it filters the worksheet so only the active row shows and it only triggers if the active row is below row 6 (I had to get help on this one too). I'm trying to use this color check function in row 6 to create dynamic headings based on the background color of the visible active row.

Please Login or Register  to view this content.

I also have a Worksheet_Deactivate() procedure. Could something here be causing the problem when I navigate away from the worksheet? This procedure clears the filters and moves the active cell to C1.

Please Login or Register  to view this content.

Any suggestions would be appreciated.

~ Phil White

Mr. Dietrick,

I am filtering rows based on the active cell. Only the row with the active cell is visible on this worksheet (call it Worksheet 3), which brings it to the top directly under row 6. The active cell that triggers the filtering is being selected by following a hyperlink from a different worksheet (worksheet 2) in the same workbook to a named range in cell A of worksheet 3.

Colors have already been applied to the cells in all rows below row 6 manually using the Format Cells function (background color fill) and once assigned, do not change. I'm trying to use the CheckColor function in row 6 to assign a heading to the cells in the active row based on their fill color. The active row may extend out as far as column P, it can vary.

The first cell in the active row that needs a dynamic header is in column C, so I am entering "=CheckColor3(C7)" in cell C6. I then copy that formula to the right so that it becomes "=CheckColor3(D7)", "=CheckColor3(E7)", etc.

When I initially enter the "=CheckColor()" formula in row 6 and copy it to the right, it returns the correct text string. When I navigate to a different worksheet and then return to worksheet 3 (with the CheckColor headings), either by following the same hyperlink, a different hyperlink to a different row, or by manually selecting the tab for worksheet 3, the result becomes #Value. Closing the workbook and reopening it has the same result regardless of whether I save or not. Neither "Calculate Now" nor F9 will refresh the function to display the correct text string.

I hope that is enough information to be of use and I again thank you for putting so much of your time into assisting me.

~ Phil White

Mr. Deitrick,

JACKPOT!

I understood how you were trying to force a recalculation by putting the RAND function in, but it didn't work for some reason. So I added an Application.Calculate statement to the end of my Sub Worksheet_Activate() procedure and now it works perfectly with the RAND function in the formula.

Thank you again for your time and patience.

~ Phil White