VB Code with charcter not numbers (xls 97)

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote