More than 3 conditional formats

[kaptep]

I Need to be able to format a large range of cells according to the number that they contain. Basically the cells are numbered 1-12 based on a variety of equations from other data and are updated frequently. I need the cells to be 12 different colors based on the value. Conditional formatting works great but only for 3 conditions. What is the easiest way to expand my conditions? I have read several tips for similar scenarios, but I cannot get any of the code to execute. Please give me some suggestions!!!

 

[parry]

My pleasure, weve certainly covered a bit of ground.

Just a quick note, if you want to use the RGB type way or a mixture of both then you dont need the icolor variable but instead use the Color or ColorIndex property depending on the colour you want for that number.

eg.

Private Sub Worksheet_Activate()
Dim c, Rng As Range

'Range where you will update formatting (NB: Only cells with a formula
'are looked at)
Set Rng = Range("A1:Z500").SpecialCells(xlCellTypeFormulas)

'Loop through every cell in the range and apply formatting
    For Each c In Rng
        Select Case c.Value
        Case 1
            c.Interior.ColorIndex = 6
        Case 2
            c.Interior.ColorIndex = 12
        Case 3
            c.Interior.Color = RGB(0, 255, 0)
        Case 4
            c.Interior.Color = RGB(255, 255, 0)
        Case 5
            c.Interior.ColorIndex = 15
        Case 6
            c.Interior.ColorIndex = 42
        Case Else
            c.Interior.ColorIndex = 0 'no color
        End Select
  Next c

'Free memory
Set Rng = Nothing

End Sub

See ya.