Coloring the cells by formula

[Guest]

I am not sure what this function is called. The cell can be programmed that
if the result is 0 or 1 or 2 or 3 - color will display instead of the number.
So 0 is black, red, green, yellow. sample formula is IF(G62="GO", 1,
IF(G62="BUY", 2, IF(G62="SELL", 3,0))) then the cell will show the
corresponding color (0,1,2,3 - black, green, yellow, red).

My questions are:
1. what is this called? so I can search in 'HELP'.

2. are there more than 4 colors (B,G,R,Y)? I like few more and their
corresponding value. How do I go about finding this out? I am looking for
navy blue (and maybe more later).

Thank you so very much.

 

[T. Valko]

It's called conditional formatting.

Depending on what version of Excel you're using you may be limited to 3
conditions. (Excel 2007 unlimited*) You have 4 conditions. To get around
that you can use 1 condition as the default condition. Looking at what you
want the default color should be BLACK. So, set the fill color of the cell
in question to BLACK.

Now, set the conditional formatting
Select the cell you want to color
Goto the menu Format>Conditional Formatting

Condition 1
Formula Is: =G62="Go"
Clcik the format button
Select the Patterns tab
Select GREEN
OK

Click the Add button and repeat the above process for the other conditions.

* limited only by available memory

Biff

 

[Gord Dibben]

It is called Format>Conditional Formatting.

In Excel versions 97 through 2003 you can have have 3 conditions......4 if you
count the default. 2007 has many more.

For a list of the colors in the 56 color palette see David McRitchie's site.

http://www.mvps.org/dmcritchie/excel/colors.htm

Bob Phillips has an add-in that allows up to 30 condtions.

http://www.xldynamic.com/source/xld.CFPlus.Download.html


Gord Dibben MS Excel MVP

 

[MyVeryOwnSelf]

I am not sure what this function is called. The cell can be

programmed that if the result is 0 or 1 or 2 or 3 - color will display
instead of the number.
So 0 is black, red, green, yellow. sample formula is IF(G62="GO", 1,
IF(G62="BUY", 2, IF(G62="SELL", 3,0))) then the cell will show the
corresponding color (0,1,2,3 - black, green, yellow, red).

My questions are:
1. what is this called? so I can search in 'HELP'.

One way is conditional formatting.

Suppose you want the color of H62 to reflect what's in G62. To use
conditional formatting, there's no need for numbers 0,1,2,3. Instead,
select H62 and start with
Format > Conditional formatting
For Condition1 in the dialog box choose "Formula Is" from the pull-down and
put
=G62="BUY"
in the box to the right. Then in the dialog box click
Format > Patterns
And choose the color you want (yellow).

For "SELL" and "GO" click "Add >>" to add more conditions, each with its
own formula and color.

2. are there more than 4 colors (B,G,R,Y)? I like few more and their
corresponding value. How do I go about finding this out? I am
looking for navy blue (and maybe more later).

Conditional formatting allows three conditions. This plus the base color
(when none of the special strings match) gives four possible colors for any
particular cell. But there are lots of colors from which to choose the
four, as was seen by clicking "Patterns."

 

[Guest]

Thank you so very much Gord. Lots of information on the links you provided.
I use Excel 2003. I need 6+ conditions.

If I put the Add-in (to get say 10 conditions/colors) on my own system, then
put the Excel file on a shared drive for others to edit. Will all the other
users see the 10 colors using their own Excel 2003? or do all other users
need the Add-In module to see the 10 conditions/colors?

Otherwise, I will need to fit the 6 conditions/colors into the limited 4.

Thanks again.

Jane
----------

 

[Gord Dibben]

The other users would need to have the add-in installed on their machines.

You could get away with more than 3 or 4 conditions if you used event code
behind the worksheet.

Similar to this which operates on data entry into column D

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("D"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "GO": Num = 10 'green
Case Is = "BUY": Num = 1 'black
Case Is = "SELL": Num = 5 'blue
Case Is = "DOG": Num = 7 'magenta
Case Is = "EAR": Num = 46 'orange
Case Is = "FOOT": Num = 3 'red
End Select
'Apply the color
With rng
.Interior.ColorIndex = Num
.Font.ColorIndex = Num
End With
Next rng
endit:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.


Gord

 

[Guest]

Dear all,

One of my colleague suggested 'using another/more bytes' to increase the
availability of color options. I am not sure what that means.

Can someone share if that is doable and how?

Thanks so much again.

Jane

 

[Gord Dibben]

Ask your colleague what is meant by that phrase and post here so's we all get an
explanation please.

You cannot increase the allowable CF limits.


Gord Dibben MS Excel MVP