How to change color in a funtion

[Guest]

Hi All
i have a function "getposition(range)" that get the position of the 4 higher
values in that range and at finnaly sums all numbers in the range.

so in my worksheet i just have to put =getposition(range) and the cell
display the correct sum result.

my problem is, i can't color the 4 higher values, that i stored the position
in my array(position).

for for an example imagine that "position" array is with the values
("","A5",A9","A10","A15")

******************************************
Function getposition(colunas) As Integer
Dim a As Range, c As Range

position = Array("","", "", "", "", "")
.....
.....
.....
.....
calcula = total

******will color the ranges /don't work

for i=1 to 4
Range(position(i)).Select
Selection.Font.ColorIndex = 5
Selection.Font.Bold = True
next

End Function

**************************

any ideas?
i can't select the cell even color them.
Thanks in advance

 

[Niek Otten]

You can not change anything in a worksheet from a function. You only return a value to replace the function call.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi All
| i have a function "getposition(range)" that get the position of the 4 higher
| values in that range and at finnaly sums all numbers in the range.
|
| so in my worksheet i just have to put =getposition(range) and the cell
| display the correct sum result.
|
| my problem is, i can't color the 4 higher values, that i stored the position
| in my array(position).
|
| for for an example imagine that "position" array is with the values
| ("","A5",A9","A10","A15")
|
| ******************************************
| Function getposition(colunas) As Integer
| Dim a As Range, c As Range
|
| position = Array("","", "", "", "", "")
| ....
| ....
| ....
| ....
| calcula = total
|
| ******will color the ranges /don't work
|
| for i=1 to 4
| Range(position(i)).Select
| Selection.Font.ColorIndex = 5
| Selection.Font.Bold = True
| next
|
| End Function
|
| **************************
|
| any ideas?
| i can't select the cell even color them.
| Thanks in advance

 

[darrenmcconachie]

Could you not colour the cells using the RANK function in conditional
formatting!?

 

[Guest]

Thanks Niek

what you advise?

i don't use any button.... there is any way to run a sub in a cell?

 

[Bob Phillips]

Use conditional formatting on all the cells with a formula of

=A1>=LARGE(A1:A100,4)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)