Hi everyone!
I have been looking for a way to force, coerce, beg a worksheet to update,
or maybe the better word is recalculate its cells either by a specific
keystroke, or better yet, when the cell output is changed.
I have been working with bypassing the conditional format limitation, and
have that taken care of. Unfortunately, the cells that have the conditional
format applied reference the calculated values in other cells. I can change
the values in the "other" cells without a problem, but the cells that I need
to change do not update the formatting.
I can select each cell individually, and hit the Enter key, and VIOLA! the
cell format changes, but it will not do it automatically.
I have looked at application.volitile, application.refreshall, but have come
up empty.
Does anyone have any suggestions?

Thanks a bunch!

There are worksheet events that are lurking waiting for something to happen.

One of them is the Worksheet_Change event. It's waiting for you to make a
change to any cell(s) via typing.

Another one is the worksheet_Calculate event. It's waiting for the worksheet to
be recalculated.

You can tie into each of these--for instance if you had a range of cells to
check each time the worksheet recalculated:

You can right click on the worksheet tab that should have this behavior and
select view code.

Then paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()

Dim myRngToCheck As Range
Dim myCell As Range
Dim myColorIndex As Long

Set myRngToCheck = Me.Range("a1,b9,c13:c15")

For Each myCell In myRngToCheck.Cells
Select Case myCell.Value
Case Is = 1: myColorIndex = 3
Case Is = 3: myColorIndex = 8
Case Is = 5: myColorIndex = 10
Case Else
myColorIndex = xlNone
End Select
myCell.Interior.ColorIndex = myColorIndex
Next myCell

End Sub

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Fleone wrote:

Hi everyone!
I have been looking for a way to force, coerce, beg a worksheet to update,
or maybe the better word is recalculate its cells either by a specific
keystroke, or better yet, when the cell output is changed.
I have been working with bypassing the conditional format limitation, and
have that taken care of. Unfortunately, the cells that have the conditional
format applied reference the calculated values in other cells. I can change
the values in the "other" cells without a problem, but the cells that I need
to change do not update the formatting.
I can select each cell individually, and hit the Enter key, and VIOLA! the
cell format changes, but it will not do it automatically.
I have looked at application.volitile, application.refreshall, but have come
up empty.
Does anyone have any suggestions?

Thanks a bunch!

--

Dave Peterson