Fixing VBA code to better suit concatenating needs!!

Let's say you have rows A through AR filled with data. Some are text others
are numeric. As the slash numbers go I would like them to read

"Sandwiches2 # from column/ # from column / # from column"

Let's say that Sandwiches2 is in column R, first # is in column P, second #
is in column V and third number is in column Z

Each has separate format (which I manipulated except for the colors because
I do not know the color indexes - they would be green, black [default], red
and [plum
and bold as one format])

Each column has the same set of colors but they are applied to different
intervals. For example, column R could have green numbers from 50 to 1000
while in column S could have green numbers from 0 to -1000

There are some things I could work out to solve my dilemma and others I
could not. Any other input would be a great help!! Thanks to all who respond!!

"JE McGimpsey" wrote:

> This would require VBA, since functions can return only values, not
> formatting, to cells.
>
> One way would be to put something like this into your Worksheet code
> module (right-click the worksheet tab and choose View Code):
>
>
> 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

Sub ShowColorIndexes()
for i = 1 to 56
cells(i,1).Interior.ColorIndex = i
cells(i,2).Value = i
Next
End Sub

--
Regards,
Tom Ogilvy


"Sandwiches2" <[email protected]> wrote in message
news:[email protected]...
> Let's say you have rows A through AR filled with data. Some are text
others
> are numeric. As the slash numbers go I would like them to read
>
> "Sandwiches2 # from column/ # from column / # from column"
>
> Let's say that Sandwiches2 is in column R, first # is in column P, second
#
> is in column V and third number is in column Z
>
> Each has separate format (which I manipulated except for the colors
because
> I do not know the color indexes - they would be green, black [default],
red
> and [plum
> and bold as one format])
>
> Each column has the same set of colors but they are applied to different
> intervals. For example, column R could have green numbers from 50 to 1000
> while in column S could have green numbers from 0 to -1000
>
> There are some things I could work out to solve my dilemma and others I
> could not. Any other input would be a great help!! Thanks to all who
respond!!
>
> "JE McGimpsey" wrote:
>
> > This would require VBA, since functions can return only values, not
> > formatting, to cells.
> >
> > One way would be to put something like this into your Worksheet code
> > module (right-click the worksheet tab and choose View Code):
> >
> >
> > 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
>