change colour based on value

[shadow12345]

what i want to do is colour the numbers in the list as the value changes, i would use conditional format but i need to have 4 colours

blank - no colour
1 = red
2 = blue
3 = green
4 = yellow

Any idea ?

TSG Ticklist.xls
	D	E	F	G
1	Alan Barnett	Kuldeep Dhanda	Katie Cotton	Robin Walker
2	3	1	3	4
3	2	4	4
4	4	4	4
5	3	4	4
6	3	4	4
7	3	4	4
8	3	4	4
CTM

 

[texasalynn]

there was many suggestions here, try a search with keys words
Conditional AND format AND multiple

 

[northwolves]

Just do a loop:

Sub macro1()
Dim mycolor, c As Range
mycolor = Array(xlNone, vbRed, vbBlue, vbGreen, vbYellow)
For Each c In ActiveSheet.UsedRange
If c.Value < 5 Then c.Font.Color = mycolor(Val(c))
'If c.Value < 5 Then c.Interior.Color = mycolor(Val(c))
Next
End Sub

Best Regards.

 

[Fergus]

Or, if you want each cell to change colour as you enter the number, put this in the relevant sheet code page:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = 1 Then
    Target.Interior.ColorIndex = 3
End If
If Target.Value = 2 Then
    Target.Interior.ColorIndex = 5
End If
If Target.Value = 3 Then
    Target.Interior.ColorIndex = 4
End If
If Target.Value = 4 Then
    Target.Interior.ColorIndex = 6
End If
If Target.Value = 0 Then
    Target.Interior.ColorIndex = xlNone
End If
End Sub

 

[shadow12345]

that last one works but if i remove the figures then i get an error, if i delete then id like it to remove the fill colour

 

[Fergus]

if i remove the figures then i get an error

Do you mean that you are removing them by selecting a whole range and hitting delete? If so try this modified code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Dim c As Range
For Each c In ActiveSheet.UsedRange
c.Interior.ColorIndex = xlNone
Next
Exit Sub
End If
If Target.Value = 1 Then
    Target.Interior.ColorIndex = 3
End If
If Target.Value = 2 Then
    Target.Interior.ColorIndex = 5
End If
If Target.Value = 3 Then
    Target.Interior.ColorIndex = 4
End If
If Target.Value = 4 Then
    Target.Interior.ColorIndex = 6
End If
If Target.Value = 0 Then
    Target.Interior.ColorIndex = xlNone
End If
End Sub

HTH

 

[Fergus]

That code I gave you will clear ALL colours, even if you only selected a small range to delete, the following code seems to be closer to what you want I think:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
    Selection.ClearContents
    Selection.Interior.ColorIndex = xlNone
    Exit Sub
End If
If Target.Value = 1 Then
    Target.Interior.ColorIndex = 3
End If
If Target.Value = 2 Then
    Target.Interior.ColorIndex = 5
End If
If Target.Value = 3 Then
    Target.Interior.ColorIndex = 4
End If
If Target.Value = 4 Then
    Target.Interior.ColorIndex = 6
End If
If Target.Value = 0 Then
    Target.Interior.ColorIndex = xlNone
End If
End Sub

HTH