Colouring cells

S

Sam A

As you can only do 3 conditional formats - I have been
pointed to donig this is VB. Any ideas!!
I want to colour cells if the value is equal to a number -
1,2,3,4 or 5. Need to be a different colour for each
result.
thanks
Sam
 
H

Harald Staff

Hi Sam

Sub test()
With ActiveCell
Select Case .Value
Case 1
.Interior.ColorIndex = 12
Case 2
.Interior.ColorIndex = 19
Case 3
.Interior.ColorIndex = 23
Case 4
.Interior.ColorIndex = 5
Case 5
.Interior.ColorIndex = 36
Case Else
.Interior.ColorIndex = xlNone
End Select
End With
End Sub

Replace "Activecell" with whatever single-cell range you want to colorize.

HTH. Best wishes Harald
 
T

Tom Ogilvy

Option Explicit

Sub ColorSelection()
Dim varr As Variant
Dim cell As Range
varr = Array(3, 6, 8, 20, 15) ' array of color indexes corresponding to
number 1 to 5
For Each cell In Selection
If IsNumeric(cell.Value) Then
If CLng(cell.Value) >= 1 And CLng(cell.Value) <= 5 Then
cell.Interior.ColorIndex = varr(CLng(cell.Value) + (LBound(varr) =
0))
End If
End If
Next
End Sub
 
Top