Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VB Code with charcter not numbers (xls 97) 1

Status
Not open for further replies.

scrappe7

Technical User
Jul 30, 2001
82
US
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 <> &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??????
 
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...
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top