change colour based on value

shadow12345

Well-known Member
Joined
May 10, 2004
Messages
1,238
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
DEFG
1Alan BarnettKuldeep DhandaKatie CottonRobin Walker
23134
3244
4444
5344
6344
7344
8344
CTM
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
there was many suggestions here, try a search with keys words
Conditional AND format AND multiple
 

northwolves

Well-known Member
Joined
Jun 21, 2006
Messages
1,122
Just do a loop:

Code:
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

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Or, if you want each cell to change colour as you enter the number, put this in the relevant sheet code page:
Code:
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

Well-known Member
Joined
May 10, 2004
Messages
1,238

ADVERTISEMENT

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

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
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:
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

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,993
Messages
5,545,378
Members
410,679
Latest member
rolandbianco
Top