Formats not updating with newly pulled data

[Stonewall Rubberbow]

I’ve run into a conditional formatting issue. I have a UDF to pull data from
other workbooks, a worksheet-change code to conditionally format my cells,
and an OnTime function that updates the pulled data every 5 minutes. The
timer works, the pull function works, and the conditional formatting works
until new data is pulled into the worksheet. Once the cell color formatting
is set, it doesn’t change with the newly pulled data. In order for me to get
the cell formatting to reflect the color choices in the code, I have to
select the cell and put my cursor into the formula and press enter, then it
will finally change colors. I’m using CalculateFull within the timer and I’ve
tried CalculateFullRebuild (although I don’t know the difference) and tried
DoEvents. I’d like for it to update the colors automatically at the time data
is updated. What can I do to trigger this? (I’m running Excel 2003, and I
don’t have the option to use the CFPlus Add-In.) Any help is much
appreciated!!!

 

[JLGWhiz]

This is from Worksheet_Change help file:

This event doesn't occur when cells change during a recalculation. Use the
Calculate event to trap a sheet recalculation.

This is from the FormatConditions help file:

Use the FormatConditions property to return a FormatConditions object. Use
the [Add] method to create a new conditional format, and use the [Modify]
method to change an existing conditional format.

From what I see, you would need to have code to modify after the initial add
takes place, and cannot use calculate to trigger the worksheet change event
if that is how you are running the format conditions code.

 

[JLGWhiz]

Of course, the modify is only if you want to change the parmeters of the
initial setting.