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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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
 

Forum statistics

Threads
1,141,937
Messages
5,709,432
Members
421,636
Latest member
kelseyacheson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top