I want to create a function that Colors the Active Cell based on a certain
criteria.
Example:
A1: 10
B1: =ColorCell(A1)

I want to use it instead of conditional formatting since I want the
flexibility - so I can use all 56 colors. Also I want =ColorCell(A1) to
return "" (=blank)

Is this possible?
Ola

You want to put a formula in B1 that will change the color of Cell A1 or
event change the color of cell B1? Unfortunately, that isn't a capability
that formulas are allowed to posses. Perhaps you want to use the calculate
event to monitor the value and change the color.

Also, how does activecell relate to A1?

--
Regards,
Tom Ogilvy



"Ola" wrote in message
...
I want to create a function that Colors the Active Cell based on a certain
criteria.
Example:
A1: 10
B1: =ColorCell(A1)

I want to use it instead of conditional formatting since I want the
flexibility - so I can use all 56 colors. Also I want =ColorCell(A1) to
return "" (=blank)

Is this possible?
Ola

Ola,

If you want more than 3 conditions, see
http://www.xldynamic.com/source/xld.....Download.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ola" wrote in message
...
I want to create a function that Colors the Active Cell based on a certain
criteria.
Example:
A1: 10
B1: =ColorCell(A1)

I want to use it instead of conditional formatting since I want the
flexibility - so I can use all 56 colors. Also I want =ColorCell(A1) to
return "" (=blank)

Is this possible?
Ola

So it is impossible. Not often you hear that.

Tom - I wanted it to work exactly as Conditional Formatting (on the active
cell), apart from controlling the ColorIndex from A1...or any other cell.
(Example: =ColorCell(C43)).
Bob - It's a good add-in, but since I'm going to send this spreadsheet to a
number of people around Europe, I can't ask them to download and install an
add-in. That's why a small VBA code had been perfect.

Thanks anyway, I appreciate the effort.
Ola

FWIW, good article: http://www.developerfusion.com/show/35/7/

No it is not impossible, the add-in shows that, but you need VBA. You can
either use the add-in out of the box, or craft your own code.

As a starter
Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
Select Case UCase(.Value)
Case 10: .Interior.ColorIndex = 5
Case 11: .Interior.ColorIndex = 46
Case 12: .Interior.ColorIndex = 7
Case 13: .Interior.ColorIndex = 3
'etc.
End Select

.value = ""

End With
End If


ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ola" wrote in message
...
So it is impossible. Not often you hear that.

Tom - I wanted it to work exactly as Conditional Formatting (on the active
cell), apart from controlling the ColorIndex from A1...or any other cell.
(Example: =ColorCell(C43)).
Bob - It's a good add-in, but since I'm going to send this spreadsheet to
a
number of people around Europe, I can't ask them to download and install
an
add-in. That's why a small VBA code had been perfect.

Thanks anyway, I appreciate the effort.
Ola

FWIW, good article: http://www.developerfusion.com/show/35/7/