Excel has a Conditional Formatting... option under the Format menu that allows you to change the style/color/font/whatever of a cell depending upon its value. But it only allows three conditions.
How do I get Excel to display say, six different background cell colors depending upon the value of the cell? (IE Make the cell red if the value is "Red", and blue if "Blue" etc.)
You will need to write something in VBA.
See example here: Get Around Excels 3 Criteria Limit in Conditional Formatting:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
Excel 2007 allows more than three conditions. Quoting from this Microsoft page.aspx):
EDIT: Ah, there's a "feature" in the linking code: parentheses in a link cited in parentheses aren't being handled correctly. That link is: http://msdn.microsoft.com/en-us/library/bb286672(office.11).aspx
Other benefits of the changes to conditional formatting in Excel 2007 are the ability to specify more than three conditions, to reorder conditions, and to have more than one condition resolve to True.
Otherwise. you're stuck with messy alternatives as described, I'm afraid.
You can use VBA macros to do this...
here is one vba macro that might be better if need lots of cases http://chandoo.org/wp/2008/10/14/more-than-3-conditional-formats-in-excel/
you need to preformat 'n' cells with the way you want to format your entire range. and then use the macro in that url to get the effect.
put this in a module in your VBA project. You can then highlight a range in a sheet and run the sub from the Tools > Macro > Macros menu item to color each cell in the selected range.
Public Sub ColorCells()
Dim cell, rng As Range
Dim color As Integer
Dim sheet As Worksheet
Application.ScreenUpdating = False
Application.StatusBar = "Coloring Cells"
Set rng = Application.Selection
Set sheet = Application.ActiveSheet
For Each cell In rng.cells
Select Case Trim(LCase(cell))
Case "blue"
color = 5
Case "red"
color = 3
Case "yellow"
color = 6
Case "green"
color = 4
Case "purple"
color = 7
Case "orange"
color = 46
Case Else
color = 0
End Select
sheet.Range(cell.Address).Interior.ColorIndex = color
Next cell
Application.ScreenUpdating = True
Application.StatusBar = "Ready"
End Sub
If users are entering new color names into cells then you could put this in the sheet code in the VBA project to color the cells as a user is entering the color names into cells
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.cells.Count > 1 Then Exit Sub
Dim color As Integer
Select Case Trim(LCase(Target))
Case "blue"
color = 5
Case "red"
color = 3
Case "yellow"
color = 6
Case "green"
color = 4
Case "purple"
color = 7
Case "orange"
color = 46
Case Else
color = 0
End Select
Target.Interior.ColorIndex = color
End Sub
EDIT: Added Trim function around the case statement expression to test, so that accidental leading/trailing spaces in cells are ignored :)