Hi all,
I have a line of code in a macro that reads as follows
Format(Evaluate("=AVERAGE(SMALL(my_rng,{1,2,3,4})) "), "0.00")
This code finds the average of the 4 smallest values in the range and
formats the result as currency. Works fine.
What I would like to do is use a similiar line of code that would
highlight those values in the worksheet.
I am thinking of something like:
Format("SMALL(my_rng,{1,2,3,4})", "Color = 49407")
but I get sytax error with this. thks in advance for any help.
BRC

I think you'll have to look at each cell in that my_rng and see if the value in
each is among the smallest 4 values.

Or maybe you could just apply conditional formatting using that rule.

If A1:A10 is my_rng, then you could select A1:A10 and use a conditional format
rule like:

=a1<=small(my_rng,4)



On 08/09/2010 15:56, BRC wrote:
Hi all,
I have a line of code in a macro that reads as follows
Format(Evaluate("=AVERAGE(SMALL(my_rng,{1,2,3,4})) "), "0.00")
This code finds the average of the 4 smallest values in the range and
formats the result as currency. Works fine.
What I would like to do is use a similiar line of code that would
highlight those values in the worksheet.
I am thinking of something like:
Format("SMALL(my_rng,{1,2,3,4})", "Color = 49407")
but I get sytax error with this. thks in advance for any help.
BRC

--
Dave Peterson

On Aug 9, 6:20*pm, Dave Peterson wrote:
I think you'll have to look at each cell in that my_rng and see if the value in
each is among the smallest 4 values.

Or maybe you could just apply conditional formatting using that rule.

If A1:A10 is my_rng, then you could select A1:A10 and use a conditional format
rule like:

=a1<=small(my_rng,4)

On 08/09/2010 15:56, BRC wrote:

Hi all,
I have a line of code in a macro *that reads as follows
Format(Evaluate("=AVERAGE(SMALL(my_rng,{1,2,3,4})) "), "0.00")
This code finds the average of the 4 smallest values in the range and
formats the result as currency. Works fine.
What I would like to do is use a similiar line of code that would
highlight those values in the worksheet.
I am thinking of something like:
Format("SMALL(my_rng,{1,2,3,4})", "Color = 49407")
but I get sytax error with this. thks in advance for any help.
BRC

--
Dave Peterson

After reading my own post i realized i left out a portion of the line
i am using as a model. that should read: myval =
Format(Evaluate("=AVERAGE(SMALL(my_rng,{1,2,3,4})) "), "0.00". What i
am trying to do is conditional formatting on these same 4 cells in
code. Thanks for suggestions

Doesn't change the solution, as posted by Dave, one iota that I can see.

--

HTH

Bob
"BRC" wrote in message
...
On Aug 9, 6:20 pm, Dave Peterson wrote:
I think you'll have to look at each cell in that my_rng and see if the
value in
each is among the smallest 4 values.

Or maybe you could just apply conditional formatting using that rule.

If A1:A10 is my_rng, then you could select A1:A10 and use a conditional
format
rule like:

=a1<=small(my_rng,4)

On 08/09/2010 15:56, BRC wrote:

Hi all,
I have a line of code in a macro that reads as follows
Format(Evaluate("=AVERAGE(SMALL(my_rng,{1,2,3,4})) "), "0.00")
This code finds the average of the 4 smallest values in the range and
formats the result as currency. Works fine.
What I would like to do is use a similiar line of code that would
highlight those values in the worksheet.
I am thinking of something like:
Format("SMALL(my_rng,{1,2,3,4})", "Color = 49407")
but I get sytax error with this. thks in advance for any help.
BRC

--
Dave Peterson

After reading my own post i realized i left out a portion of the line
i am using as a model. that should read: myval =
Format(Evaluate("=AVERAGE(SMALL(my_rng,{1,2,3,4})) "), "0.00". What i
am trying to do is conditional formatting on these same 4 cells in
code. Thanks for suggestions