code for characters not numbers, dialouge included

[scrappe7]

scrappe7 (TechnicalUser) Aug 7, 2001
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&quot.Select 'Cells to enter dates are labeled as a range "NUM"
For Each ncell In Selection
If ncell.Value <> &quot;&quot; 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 <> &quot;&quot; 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(&quot;Detail&quot.Select 'Cells that change ID# to text are labeled as a range &quot;Detail&quot;
For Each dcell In Selection
If dcell.Value <> &quot;&quot; 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????


Databaseguy (MIS) Aug 7, 2001
I cut and pasted your code and it worked first try. Do you code anywhere else in the module ?? Go to Debug/Compile from the Visual Basic Editor (VBE) and see where it hangs...

MY RESPONSE

scrappe7 (TechnicalUser) Aug 7, 2001
I have code on my NUM sheet that contains the project ID#'s. I was entering this code on the DETAIL sheet. the DETAIL sheet is where the project title is displayed instead of the number. This is the one i want to background fill. There is nothing else anywhere. Shouldnt i be entering the code for the worksheet i want in that sheets code page? i did the compile thing but nothing happened. I hope this answers your question and that you can figure out what im missing, thanks alot.

 

[Niv3k]

Try adding this right before the Select Case:

Dim vnt as Variant

vnt = dcell.Value
Select Case vnt
    Case 1
    ...
    Case "Project Name"
    ...
    Case 2
    ...
End Select

If that doesn't work try
Case &quot;1&quot;
Case &quot;2&quot;
etc...

Kevin

 

[scrappe7]

does my colorindex value go where the ... lies?
where it says &quot;Project Name&quot; what should i enter there? the sheet gets the project name using vlookup already, i just want a color based on the project name. I'm probably sounding dumb now but you hvae to understand me and VB are not best of friends yet. A little more detail would be extremly helpful, thanks a lot.

Ed