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 <7F6FFE95-6313-4DDD-8F76-6AA2B16DECDC@microsoft.com>,
> Sandwiches2 <Sandwiches2@discussions.microsoft.com> 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!
>