Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB code (xls 97)

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VB code (xls 97)

    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

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code (xls 97)

    Your code could be modified as follows:

    <pre>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
    </pre>


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

    <pre>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
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code (xls 97)

    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

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code (xls 97)

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code (xls 97)

    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.

  7. #7
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code (xls 97)

    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

  8. #8
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code (xls 97)

    <P ID="del"><FONT SIZE=-1>Post deleted by gwhitfield</FONT>

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code (xls 97)

    Try this:

    <pre>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

    </pre>

    Legare Coleman

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code (xls 97)

    Well, firstly, you'll need to expand the number of numbers <g> 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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code (xls 97)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •