1ST MESSAGE: (ORIGINAL QUESTION)
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!
2ND MESSAGE (TIP I RECEIVED):
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.
3RD MESSAGE (MY REPLY):
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
WHAT DO I DO FROM HERE??????
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!
2ND MESSAGE (TIP I RECEIVED):
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.
3RD MESSAGE (MY REPLY):
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
WHAT DO I DO FROM HERE??????