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.