Using VB in Excel 2003  
Author Message
Thomas Moore





PostPosted: Visual Basic for Applications (VBA), Using VB in Excel 2003 Top

Can anyone help me rework this code. What I need is the code to call four 
columns. First is a name column. Then a space then 3 numbers separated by 
slashes each with a different rule for coloring.
 
So example let’s say name is Smith
 
I want  Smith 32/15/3.4
 
And the column where 32 comes from let’s say has parameters 20+ is green, 
15-19 is black, 10-14 is red and below 10 is plum and bold. Second column 
number would be similar except that the parameters would change. So 9-12 
would be green, 13-14 black, 15-17 red and 18+ plum and bold
 
My feeling is that I would need three if statements for the colors, but I 
can’t seem to append the three numbers to a single column with color codes 
intact. Please help!
 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
      Const sSep As String = "/"
      Dim nLen(1 To 3) As Long
      Dim nColorIndex As Long
      Dim nPos As Long
      Dim i As Long
      Dim dValue(1 To 3) As Double
      Dim sTemp As String
      Dim sVal As String
      Dim bBold As Boolean
      With Range("A1:C1")
         For i = 1 To 3
            sVal = .Item(i).Text
            nLen(i) = Len(sVal)
            If IsNumeric(sVal) Then dValue(i) = CDbl(sVal)
            sTemp = sTemp & sSep & sVal
         Next i
      End With
      On Error GoTo ErrHandler
      Application.EnableEvents = False
      With Range("J10")      'Destination Cell
         .ClearFormats

         .Value = Mid(sTemp, 2)
         nPos = 1
         For i = 1 To 3
            If nLen(i) > 0 Then
               Select Case dValue(i)
                  Case Is < 3
                     nColorIndex = 5 'default blue
                     bBold = True
                  Case Is >= 15
                     nColorIndex = 10  'default green
                     bBold = False
                  Case Else
                     nColorIndex = xlColorIndexAutomatic
                     bBold = False
               End Select
               With .Characters(nPos, nLen(i)).Font
                  .Bold = bBold
                  .ColorIndex = nColorIndex
               End With
            End If
            nPos = nPos + nLen(i) + Len(sSep)
         Next i
      End With
   ErrHandler:
      Application.EnableEvents = True
   End Sub
I run XP, microsoft excel 2003. I've played around with the code many times but all I get is 
#//# in the specified cell instead of NAME #/#/#. Please help, the attached code somewhat works but it is

Your help will be greatly appreciated.


Microsoft ISV Community Center Forums2  
 
 
spotty





PostPosted: Visual Basic for Applications (VBA), Using VB in Excel 2003 Top

From looking at the code and the title it would appear that your trying to do this all within Excel. However this version of VB which is within Excel is called VBA and is very different from VB.Net product that this forum is intended to cover. Its much more limited and based upon technology in older versions of Visual Basic.

You may have more success with finding answers on VBA for the office products by using the following resources

Office Automation:

http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.office.developer.automation&lang=en&cr=US

There also appears to be a forum specific to VBA

http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=74&SiteID=1

If you want to to build a VB.NET based solution in (Outlook, Sharepoint, Infopath, Word, or Excel), then maybe the VSTO (Trinity) forums are a good bet: http://forums.microsoft.com/MSDN/default.aspx forumgroupid=4&siteid=1


 
 
Jon Peltier





PostPosted: Visual Basic for Applications (VBA), Using VB in Excel 2003 Top

 
From looking at the code and the title it would appear that your trying to do this all within Excel.   However this version of VB which is within Excel is called VBA and is very different from VB.Net product that this forum is intended to cover.

Again, one of us is missing something. The breadcrumbs tell me that the forum is intended for VBA:

MSDN Forums Microsoft ISV Community Center Forums Visual Basic for Applications (VBA) Re: Using VB in Excel 2003

To the original question. Early in the code you are concatenating cell values with slashes to obtain

/#/#/#

and later you remove the first character to get

#/#/#

If the middle # is nonexistent, you should make sure there is a numerical value in cell B1. All the fancy formatting after that should not change the actual text in the cell.

Are you successful in using VBA to obtain such a variety of formats within a given cell In the past I've had difficulties beyond just a couple different formats (although that included font name, style, and size in addition to color).


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______



 
 
MS ISV Buddy Team





PostPosted: Visual Basic for Applications (VBA), Using VB in Excel 2003 Top

I guess it's possible that the original question might have been moved from the VB or VB.Net forums.

-brenda (ISV Buddy Team)



 
 
K42





PostPosted: Visual Basic for Applications (VBA), Using VB in Excel 2003 Top

I apologize for this reply not pertaining to your question but I can't seem to get to post a question. I thought you may be able to help me. I am tyring to create a flashing cell when any text is entered in. I have been playing with VB and having a lot of fun but I am not having any luck with this. Also how do you make the macro run automatically All I can do is F5 and then go back to excel to see the change. Any help would be greatly appreciated.