Calculate on Format Change

rvences

New Member
Joined
Feb 21, 2002
Messages
9
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?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 ?



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.
 
Upvote 0
Hi again rvences,

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

Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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