Conditional formatting alternatives?

[deko]

I have a worksheet that looks something like this:

A2:A30 contain standard deviations for each row, and B2:G30 contain the
numbers (this example is somewhat oversimplified).

What I need to do is this:

1. Insert a control value in A32 (e.g. 2 or 3)
2. Apply formatting to B2:G30 where cells with values greater or less than
the control value multiplied by the respective standard deviation for each
row are highlighted (is there a variance function for this?)

Users needs to be able to change the default control value by entering a new
value in A32, which updates the formatting accordingly.

I'm wondering if conditional formatting is the best solution here. For one,
users don't know how to use it and secondly, the worksheets are massive -
often containing over 200 worksheets with hundreds of rows and dozens of
columns.

Would it be better to have some code (rather than conditional formatting)
apply the formatting whenever a particular worksheet is activated? Or
should I just insert the control value and apply conditional formatting when
each worksheet is activated?

If anyone can provide or point me to code examples that would be a big help.

Thanks in advance.

 

[Vasant Nanavati]

Conditional formatting should work just fine:

To cell B2, apply the condition:

=Cell Value Is greater than =$A2*$A$32

and an appropriate format.

Copy the formatting to B2:G30.