How to reactivate formula in cell with VB following error

[bubastisbastet]

Hi Forum Members,

I have a spreadsheet in which a particular range (say A1:A100) dynamically changes between 2 states:

1) the "blank" state: range is 100% filled with a pattern, or
2) the "active" state: range is filled to varying degrees with a specific color (in this case color index 37)

So for example, the user may initiate the active state (via VB) and then enter data. if changes were to be made, they would clear all cell contents (via VB) and revert to the "blank" state, re-activate the "active state", and then re-enter their revised data.

I want to utilise a series of "if then" statements that will distinguish (amongst other things) between the 2 states. in order to distinguish the "active" state, I am using the following code (in cell B1 let's say) to determine if any of the cells are colored:

=colorindexofrange(A1:A100,false,1) (as array formula)

if any cells are colored, it will return "37".

this code is generously supplied by chip pearson here:

http://www.cpearson.com/excel/colors.aspx


so far, so good.

the problem is the following:

if the "blank" state is invoked, B1 will return an error (as it can't recognize pattern). upon re-activating the "active state", the error remains UNTIL the formula is re-calculated by performing select/double click/enter in B1.

I am not able to re-calculate B1 using VB:

range("b1").select
application.doubleclick

or

range("b1").select
application.calculate

so how can i re-calculate B1 using VB??

Many thanks for any help you guys can offer!!

 

[VoG]

Possibly

Range("B1").Calculate

 

[Richard Schollar]

Range("B1").Calculate

 

[bubastisbastet]

Just the trick! - thank you very much!