<P ID="edit"><FONT SIZE=-1>Edited by Andrew Cronnolly on 16-Aug-01 16:10.</FONT></P>This post is duplicate on the Excel board in [<A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=64827&page=0&view =expanded&sb=5&o=0&part=>This Thread</A> - Please post replies to Excel Board.

hi all. i have this code that when the macro is run it assigns a background color to that cell based on its color. The code is lengthy but works fine. However, a problem arises when a cell that had a number in it when the macro was run (ie A1 had 7 in it and thus turned blue). Now if that 7 gets deleted from the cell, it still remains blue the next time the macro gets run. I want the cells with no number in them to stay or return to no fill (ie colorindex = 0). heres a sample of my code, where should i enter a new line it to do what i need. thanks.



Sub NumColors()
Dim x As Integer
Dim i As Integer
Dim ncell As Range
Range("Num").Select 'Cells to enter dates are labeled as a range "NUM"
For Each ncell In Selection
If ncell.Value <> "" Then ' If cell is blank then it gets skipped

For i = 1 To 250

If ncell.Value = i Then 'sets cell# to specific color

If i = 1 Then
ncell.Interior.ColorIndex = 3 ' color codes 0,1,2,5,11,18,21,25,50,51,52,53,55,56 are bad for background color, don't use
ElseIf i = 2 Then
ncell.Interior.ColorIndex = 4 ' color code can not be higher than 56
ElseIf i = 3 Then
ncell.Interior.ColorIndex = 6
ElseIf i = 4 Then
ncell.Interior.ColorIndex = 7
ElseIf i = 5 Then
ncell.Interior.ColorIndex = 8
ElseIf i = 6 Then
ncell.Interior.ColorIndex = 32
ElseIf i = 7 Then
ncell.Interior.ColorIndex = 10
ElseIf i = 8 Then
ncell.Interior.ColorIndex = 12

End If
End If
Next i
End If
Next ncell
End Sub