Conditional formatting alternatives?

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.

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.

--

Vasant

"deko" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>