VB code (xls 97)

Topic

New Reply

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

Reply | Quote

Replies

You could replace

If ncell.Value “” the 7th line of the code posted by you

with

If ncell.Value = “” Then ‘ If cell is blank then it gets skipped
ncell.Interior.ColorIndex = xlNone
Else

What is the significance of the For i = 1 To 250 Loop ?

Andrew C

Reply | Quote

the cells can have values from 1 to 250. so i used i to count them for me. i only pasted a portion of my code, it extends a lot further. i know there is a cleaner way to do this but im still learning so i take what i can get, your knowledge worked perfectly, again.

Ed

Reply | Quote

Post deleted by gwhitfield

Reply | Quote

Try this:

Sub NumColors()
Dim x As Integer
Dim i As Integer
Dim ncell As Range
Dim vColors As Variant
    vColors = Array(3, 4, 6, 7, 8, 10, 12)
    'Cells to enter dates are labeled as a range "NUM"
    For Each ncell In Range("Num")
        If ncell.Value  "" Then ' If cell is blank then it gets skipped
            ncell.Interior.ColorIndex = vColors(ncell.Value Mod (UBound(vColors) + 1))
        Else
            ncell.Interior.ColorIndex = xlNone
        End If
    Next ncell
End Sub

Reply | Quote

Your code could be modified as follows:

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
                    ElseIf i = 2 Then
                        ncell.Interior.ColorIndex = 4
                    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
        Else
            ncell.Interior.ColorIndex = xlNone
        End If
    Next ncell
End Sub

However, I think the following code will do the same thing much faster:

Sub NumColors()
    For Each ncell In Range("Num")
        Select Case ncell.Value
            Case 1
                ncell.Interior.ColorIndex = 3
            Case 2
                ncell.Interior.ColorIndex = 4
            Case 3
                ncell.Interior.ColorIndex = 6
            Case 4
                ncell.Interior.ColorIndex = 7
            Case 5
                ncell.Interior.ColorIndex = 8
            Case 6
                ncell.Interior.ColorIndex = 32
            Case 7
                ncell.Interior.ColorIndex = 10
            Case 8
                ncell.Interior.ColorIndex = 12
            Case Else
                ncell.Interior.ColorIndex = xlNone
        End Select
    Next ncell
End Sub

Reply | Quote

Wouldn’t this be more efficient:

Sub NumColors()
Dim x As Integer
Dim i As Integer
Dim ncell As Range
Dim vColours As Variant
vColours = Array(3, 4, 6, 7, 8, 32, 10, 12)
‘Cells to enter dates are labeled as a range “NUM”
For Each ncell In Worksheets(“sheet1”).[num]
If ncell.Value “” Or ncell.Value > 8 Then ‘ If cell is blank then it gets skipped
ncell.Interior.ColorIndex = vColours(ncell.Value)
Else
ncell.Interior.ColorIndex = xlNone
End If
Next ncell
End Sub

Reply | Quote

Pieterse, I edited your suggestion to thefollowing

Sub NumColors()
Dim x As Integer
Dim i As Integer
Dim ncell As Range
Dim vColors As Variant
vColors = Array(3, 4, 6, 7, 8, 10, 12)
‘Cells to enter dates are labeled as a range “NUM”
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
ncell.Interior.ColorIndex = vColors(ncell.Value)
Else
ncell.Interior.ColorIndex = xlNone
End If
Next ncell
End Sub

This line is causing the problem
ncell.Interior.ColorIndex = vColors(ncell.Value)

I get an out of range error if left alone.
If i take out the (ncell.value) it colors everything in as 3(red). If i have 100 different values i need them have different colors based on the array? how can i make 100 different ncell values have at least 30-40 different colors based on what i enter in the array??
thanks for the help.

Reply | Quote

Well, firstly, you’ll need to expand the number of numbers assigned to the vColors array to get 30 colors.

Then you will need some sort of translation from the 100 numbers to a color index number (the index pointing to color number n in the array).

Now the macro just uses the value of the cell directly, which means that you’ll get a subscript out of range error as soon as the number in the cell is greater than the number of colors defined in the array (>7 in this case).

So if you’ve established a relation between the cell value and the color number you need, you can calculate the color nmber from the cell’s value and act accordingly.

Reply | Quote

Another remark:

You edited my code, which is OK of course, but it is not necessary to select the range Num to do what you want. My original code works more efficient since it does not select the range. What you do need to do is make sure the sheetname in my original code is changed to match your situation.
Furthermore I added a check in the code to make sure numbers greater than 7 were to be skipped, thus preventing errors like the one you had. Since you need numbers greater than 7, my code needs adjusting there.

Reply | Quote

Could you help witht he following, you helped with this code before

Sub NumColors()
Dim x As Integer
Dim i As Integer
Dim ncell As Range
Dim vColors As Variant
vColors = Array(3, 4, 6, 7, 8, 10, 12)
‘Cells to enter dates are labeled as a range “NUM”
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
ncell.Interior.ColorIndex = vColors(ncell.Value)
Else
ncell.Interior.ColorIndex = xlNone
End If
Next ncell
End Sub

This line is causing the problem
ncell.Interior.ColorIndex = vColors(ncell.Value)

I get an out of range error if left that way
If i take out the (ncell.value) it colors everything in as 3(red). If i have 100 different values i need them have different colors based on the array? how can i make 100 different ncell values have at least 30-40 different colors based on what i enter in the array??
thanks for the help.

Ed

Reply | Quote