Hey JE,

Thanks for the help, it's very close to what I need. But if I could bother
you one last time for exaclty what I need that would be great.

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 excpet for the colors because
I do not know the color indexes - they would be green, black, red and [plum
and bold as one])

There are some things I could work out to solve my dilemma and others I
could not (such as making the code look at the specified range). Any other
input would be a great help!!

"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

Set your number formats in A1:C1 for the number of decimal places you'd
like.

Change cell references to suit.


In article ,
Sandwiches2 wrote:

This is a wee-bit more complex than my title sounds. Basically, I want to
take numbers from different cells - let's say A1, B1 and C1 and place them
into a single cell. Here is the tricky part: the numbers are decimals, let's
say anything from
-.028 to 27.6. I have excel round the numbers to no decimal places and I
also have conditional formatting set for numbers within a certain range. For
example 15+ could be green while below 3 could be blue and bold. I want to
pull the numbers exactly as displayed into a single cell like this A1/B1/C1
while ROUNDED & COLORED. Is there anyway I can do that, VBA or otherwise. (If
VBA please be thorough as I have a limited background). Thanks!