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

Fixing VBA code to better suit concatenating needs!!
 

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" wrote in message
...
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