conditional coloring the current cell

[ciprian]

Hi,

I try to color the cell in witch i run the function based on the results
from others cells below it.

the problem: on A1 i have the function =color_me( A2:A10 )
in A2 throw A10 i have different texts wit different color, i want to color
A1 with the most "important" color from the range (green, white, blue, red) i
chose based on different critera.
I manage to get the color, but i cant "write" the color to A1. Tried with
..Interior.ColorIndex but nothing

Anyone can help?

Thanks

 

[Nigel]

You need to supply the function code, and the criteria you refer to....also
what version of xl are you using, color management is far easier in xl2007

 

[ciprian]

yes sorry, here is the code, and i use office2003, in this version i modify
the cell above the cell the function in running into.

Do exists a pointer to the running cell? something like ActiveCell?

Public Function Free(rRng As Range) As String

Dim FreeOuiNon As Boolean
Dim color As Long
Dim RetColor As Long


FreeOuiNon = True
RetColor = 9999
color = -1

topCol = rRng.Column
topRow = rRng.Row - 2


For Each c In rRng.Cells
If (IsEmpty(c.Value) And FreeOuiNon) Then
FreeOuiNon = True
Else
FreeOuiNon = False
color = c.Interior.ColorIndex
End If

' red = 3, green = 4, white = -4142
If (color > 0) And (color < RetColor) Then RetColor = color
Next c


If FreeOuiNon = True Then
Free = RetColor
Else
Cells(topRow, topCol).Interior.ColorIndex = RetColor
Cells(topRow, topCol).Interior.Pattern = xlSolid

Free = RetColor
End If

End Function