Calculate on Format Change

[rvences]

I created a custom function (using VBA) that returns the Excel Color Index of a particular cell. The function works fine, however, when changing the color format of my "target cell" my custom function does not update to indicate the target cell's new color index. I only get the update once I manually recalculate (F9) or the spreadsheet recalculates by entering data into another cell. My auto calculate is enabled, and I did use the "Application.Volatile" feature in my code. Apparently a format change alone does not cause Excel to recalculate the spread sheet. I've also tried adding "calculate" to my code, which doesn't work. How can I get my custom function to "refresh" after a format change to a cell?

[Damon Ostrander]

Hi rvences,

Yes, calculation is not supposed to occur upon operations that do not change the contents of cells. However, you can capture such an event (it is not a real Excel event) using "trickery". Fortunately you asked at the right time (April 1st).

First of all, you have to know how the cell format has been changed (i.e., manually vs. via a macro). If it is changed via a macro then it is easy: just re-calculate right after the macro changes the color using the worksheet object's Calculate method. If it has changed because the cell format has been manually set, it requires the trickery I referred to. You can take advantage of the fact that the format cannot be manually edited without selecting the cell first. What you have to do is use the worksheet's SelectionChange event to determine if the last (previous) cell selected was the one you are interested in. In your SelectionChange event routine you must save a pointer to the Target range. If the Target cell is the cell you are interested in you must also save the current value of the format property you are interested in (e.g., ColorIndex). Then when the next cell is selected, simply check to see if the saved cell pointer is the cell of interest. If and only if this is so the format could have been edited. So then check the format to see if it has been changed, and if so, force the worksheet to re-calculate.

Here's an example that recalculates the sheet whenever the color fill of cell B4 is manually changed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static LastRange As Range 'The last range selected
Static LastColorIndex As Integer 'The color index of the last range selected
If Not Intersect([B4], LastRange) Is Nothing Then
If LastRange.Cells(1).Interior.ColorIndex <> LastColorIndex Then
Calculate
End If
End If
Set LastRange = Target
LastColorIndex = Target.Interior.ColorIndex
End Sub

Incidentally, I should mention that the color fill property of a cell is not changed via Conditional Formatting, so none of this applies to Conditional Formatting, but this shouldn't matter anyway since Conditional Formatting occurs based on cell content changes that trigger worksheet re-calculation.

[rvences]

Thanks for the tip! How would I incorporate this into a custom vba function? Would I call the macro from within the function?

Thanks,
Rvences

[Damon Ostrander]

Hi again rvences,

You would not need to incorporate this into your function. Just put the code into the worksheet's event code module (right-click on the worksheet's tab, select View Code, and insert the code into the code pane). This routine will cause the worksheet to recalculate whenever the cell (in this example B4) fill color changes, and therefore (because you have Volatile set) your function will calculate and return the new color index (or whatever).

Happy computing.

Damon

[rvences]

Thanks once again. I hate to be a pest, but I have yet another question. I'm trying to use the custom function as an Add In so that I can pass it around to several users. You suggest adding your code (or a modified version of your code) to my workbook, would this work by adding the code to an XLA file, then passing out the Add IN? Or, will the code only work for one specific workbook (i.e. the custom function would not be "global")?

I appreciate all your help and patience.

Rvences

[Damon Ostrander]

Hi again rvences,

Unfortunately, the code would not work as an add-in because it is based on a worksheet event and so has to reside in the worksheet's event code module. In addition, I cannot envision any way to make it more general such that it could be made into an add-in or even a workbook-level macro.

Damon

[rvences]

I really apprecite the help. I'll just let the users know that they need to recalc the worksheet before the custom formula will work properly. Again, thanks for the tips!!

Rvences

[MikeHshm]

Damon,

I found your old posting below which nearly does what I need -

I would like to force a full recalculation if someone changes the colour of any cell - could your sub be modified to do this ?

Originally Posted by Damon Ostrander

Hi rvences,

Yes, calculation is not supposed to occur upon operations that do not change the contents of cells. However, you can capture such an event (it is not a real Excel event) using "trickery". Fortunately you asked at the right time (April 1st).

First of all, you have to know how the cell format has been changed (i.e., manually vs. via a macro). If it is changed via a macro then it is easy: just re-calculate right after the macro changes the color using the worksheet object's Calculate method. If it has changed because the cell format has been manually set, it requires the trickery I referred to. You can take advantage of the fact that the format cannot be manually edited without selecting the cell first. What you have to do is use the worksheet's SelectionChange event to determine if the last (previous) cell selected was the one you are interested in. In your SelectionChange event routine you must save a pointer to the Target range. If the Target cell is the cell you are interested in you must also save the current value of the format property you are interested in (e.g., ColorIndex). Then when the next cell is selected, simply check to see if the saved cell pointer is the cell of interest. If and only if this is so the format could have been edited. So then check the format to see if it has been changed, and if so, force the worksheet to re-calculate.

Here's an example that recalculates the sheet whenever the color fill of cell B4 is manually changed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static LastRange As Range 'The last range selected
Static LastColorIndex As Integer 'The color index of the last range selected
If Not Intersect([B4], LastRange) Is Nothing Then
If LastRange.Cells(1).Interior.ColorIndex <> LastColorIndex Then
Calculate
End If
End If
Set LastRange = Target
LastColorIndex = Target.Interior.ColorIndex
End Sub

Incidentally, I should mention that the color fill property of a cell is not changed via Conditional Formatting, so none of this applies to Conditional Formatting, but this shouldn't matter anyway since Conditional Formatting occurs based on cell content changes that trigger worksheet re-calculation.

[Damon Ostrander]

Hi again rvences,

I haven't check out this code, but I think it should do it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Static LastRange As Range 'The last range selected
   Static LastColorIndex As Integer 'The color index of the last range selected

   If LastRange.Cells(1).Interior.ColorIndex <> LastColorIndex Then
      Application.CalculateFull
   End If

   Set LastRange = Target
   LastColorIndex = Target.Interior.ColorIndex

End Sub

Damon

[Tahas]

Hi rvences,

Were you able to resolve your issue? I have a similar problem and not able to resolve it.

Damon, thanks for your valuable in put
Taha