Highlighting (formatting) 4 lowest cells in a named range to differntcolor

[BRC]

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]

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)

 

[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)

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

 

[Bob Phillips]

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

--

HTH

Bob

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)

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