Having differents colors

[Blair]

Hi All,

I was wondering if it is possible to have differents colors for each
"Statement" in the following formula?

=IF(O2=3,"$10 Winner",IF(O2=4,"4 OUT OF 6",IF(O2=5,"5 OUT OF
6",IF(O2=6,"WE'RE IN THE MONEY!!!",0))))

For example, if "$10 Winner", could be red, "4 OUT OF 6", could be black,
etc.....

It's not critical that this happen in this particular cell, but I thought it
would be a jazzier way to show each statement. Just wondering.

Thanks in advance for any info.

Blair

 

[Chip Pearson]

Blair,

You can't do it with a formula. Formulas can only return a value; they can't
change any attribute of a cell. You can use Conditional Formatting, on the
Format Menu, to change the format based on the value of a cell. See
http://www.contextures.com/xlCondFormat01.html for more details.

 

[David McRitchie]

Hi Blair,
You can't use Conditional Formatting because of the 3 conditions
per cell limitation. Since you have a formula involved you would
have to use the Calculation event for the change to to occur.

Since I wouldn't want to read anything with a Red background
I will assume you by color change you mean font.
.interior.colorindex or .font.colorindex

Retaining your formula in Column P but I think you want
"" instead of 0 at the right side. Column P is column 16.

=IF(O2=3,"$10 Winner",IF(O2=4,"4 OUT OF 6",IF(O2=5,"5 OUT OF 6",IF(O2=6,"WE'RE IN THE MONEY!!!",""))))

Event macros are installed differently than standard macros,
install the following by right click on worksheet tab, view code,
then insert the following.

Option Explicit
Private Sub Worksheet_Calculate()
'-- formula P2:
'-- =IF(O2=3,"$10 Winner",IF(O2=4,"4 OUT OF 6",IF(O2=5,"5 OUT OF 6",IF(O2=6,"WE'RE IN THE MONEY!!!",""))))
Dim cell As Range
Dim vColor As Long
For Each cell In Intersect(Columns("O"), ActiveSheet.UsedRange)
Select Case cell.Value
Case ""
vColor = -4142
Case 6
vColor = 3 'Red
Case 5
vColor = 7 'Magenta / Fushia / Excel Pink
Case 4
vColor = 38 'Rose as seen in Colro Palette
Case 3
vColor = 40 'Tan as seen in Color Palette
Case Else
vColor = -4142
End Select
'-- tested column O, change font in col P, change interior color in Col Q
'-- hopefully your column Q is empty so colors won't make reading difficult.
'Excel 2002 has format as a change event, don't want to trigger it
On Error Resume Next
Application.EnableEvents = False 'should be part of Change macro
cell.Offset(0, 1).Font.ColorIndex = vColor
cell.Offset(0, 2).Interior.ColorIndex = vColor
Application.EnableEvents = True 'should be part of Change macro
On Error GoTo 0
Next cell
End Sub
'-- ColorIndex http://www.mvps.org/dmcritchie/excel/colors.htm
'-- Event http://www.mvps.org/dmcritchie/excel/event.htm

The code above was adapted form my
http://www.mvps.org/dmcritchie/excel/event.htm#change

I have tried to make the macro safe for someone not used to
macros, but if the above fails you may have to install the
following in a standard module and run it to get Events back.

Problems (#problems)
Note the Change Event macros that change data should include
code to turn off EnableEvents to prevent triggering another
Change Event. Once set to False you cannot run another Event
macro nor can you run you Event macro after you fix a problem with
your Event macro. You would have to run a macro to put EnableEvents
back to True. Directions to install in getstarted.htm on my site.

Sub Fix_Things()
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

A more complete macro can be seen in the reset_things macro in
http://www.mvps.org/dmcritchie/excel/code/proper.txt
--

 

[Debra Dalgleish]

With your revision ("" instead of 0), the cell could be formatted with
one font colour, and the remaining three colours could be applied with
conditional formatting.