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

    VB Code with charcter not numbers (xls 97)

    I am running a macro in VB that auto colors a range of cells based on the ID number that is in that cell. I have then taken this sheet and using VLOOKUP, made a duplicate sheet that displays the project title instead of the ID number. My coding works fiine for the number table, but i don't know how to write to same code for the sheet that has the project titles in it. Here is a sample of my coding from the ID macro:

    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
    End If
    End If
    Next i
    End If
    Next ncell
    End Sub


    If i could somehow change a line or two to make it check for i as a text value instead of a number that would work. Or if i could use the cell that the title is referencing from (VLOOKUP) to display the background color that would work tooo i think. thanks alot!

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Virginia Beach, Virginia, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code with charcter not numbers (xls 97)

    I would replace your IF THEN ELSEIF with a SELECT CASE statement and eliminate the FOR i = to to 250.

    If ncell.Value <> "" Then ' If cell is blank then it gets skipped
    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 Else
    End Select
    End If

    This lets you do other things with non-numeric cell values if you want.

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

    Re: VB Code with charcter not numbers (xls 97)

    I have tried what u said but i get a run time error, heres how the code looks:

    Sub DetailColor()
    Dim dcell As Range
    Range("Detail").Select 'Cells that change ID# to text are labeled as a range "Detail"
    For Each dcell In Selection
    If dcell.Value <> "" Then ' If cell is blank then it gets skipped
    Select Case dcell.Value
    Case 1 '***HERE THE ERROR OCCURS***
    dcell.Interior.ColorIndex = 3
    Case 2
    dcell.Interior.ColorIndex = 4
    Case 3
    dcell.Interior.ColorIndex = 6
    Case 4
    dcell.Interior.ColorIndex = 7
    Case Else
    End Select
    End If
    Next dcell
    End Sub

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Virginia Beach, Virginia, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code with charcter not numbers (xls 97)

    What is the error. I tried to duplicate, but i didn't get an error.

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

    Re: VB Code with charcter not numbers (xls 97)

    I figured it out using macro's that take the colors form the ID sheet and combines it with the text from the Detailed sheet, it works ine. no need for VB here, thank a million though.

Posting Permissions

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