Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    conditional cell coloring ('97, SR-2)

    a cell will contain an integer between 1 and 9.
    I want the cell to have a different color depending on the integer value, hence 9 color possibilities.
    As I understand, conditional formatiing can only handle up to 3 different conditions...
    How can I do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: conditional cell coloring ('97, SR-2)

    You can use a worksheet level event handler.
    - Activate the Visual Basic Editor (Alt+F11)
    - Activate the Project Explorer (Ctrl+R)
    - Double click the item corresponding to the worksheet containing the cell.
    - Copy the following code into the module, replacing all occurrences of A1 by the cell you want to color.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    Range("A1").Interior.ColorIndex = Range("A1").Value + 33
    End If
    End Sub

    You can experiment with more complicated color settings:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    Select Case Range("A1")
    Case 1
    Range("A1").Interior.ColorIndex = 2
    Case 2
    Range("A1").Interior.ColorIndex = 40
    Case 3
    Range("A1").Interior.ColorIndex = 34
    Case 4
    Range("A1").Interior.ColorIndex = 41
    Case 5
    Range("A1").Interior.ColorIndex = 35
    Case 6
    Range("A1").Interior.ColorIndex = 42
    Case 7
    Range("A1").Interior.ColorIndex = 36
    Case 8
    Range("A1").Interior.ColorIndex = 39
    Case 9
    Range("A1").Interior.ColorIndex = 37
    End Select
    End If
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •