Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming in excel
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 lets say name is Smith I want Smith 32/15/3.4 And the column where 32 comes from lets 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 cant seem to append the three numbers to a single column. 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 .NumberFormat = "@" .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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Or Excel Programming | Excel Worksheet Functions | |||
Programming Excel with C++ .Net (MFC) | Excel Programming | |||
vfp - excel programming | Excel Programming | |||
Excel Programming | Excel Programming | |||
Excel programming not using COM | Excel Programming |