concatenate formatted cells in excel

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!

=CONCATENATE(A1,B1,C1)

I have never heard of having multiple colors in one cell

you can also use

=A1&" "&B1&" "&C1

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 <[email protected]>,
Sandwiches2 <[email protected]> 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!

On Mon, 13 Mar 2006 17:45:14 -0800, Sandwiches2
<[email protected]> 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!

This can be done, but you would have to include the conditional formatting
criteria in your macro. As far as I know, there's no other way to detect the
color other than by examining the ConditionalFormatting object

Also, you will have to use a Sub and not a Function, as Functions can only
return values and cannot alter the attributes of the cell or contents.

For example:

==============================
Option Explicit
Sub ConcatAndFormat()
'Concatenate a selection
'Need to add error check that selection is correct size

Dim c As Range, ResCell As Range
Dim str As String
Const sep As String = ", "
Dim lenText As Long, stText As Long
Dim Temp As Variant, i As Long

'Concatenate the string
For Each c In Selection
str = str & _
Application.WorksheetFunction.Round(c.Value, 0) _
& sep
Next c
str = Left(str, Len(str) - Len(sep))

'Store it in cell to right of selection
Set ResCell = Selection.Offset(0, Selection.Columns.Count)
Set ResCell = ResCell.Resize(1, 1)
ResCell.Value = str

'Get formats and apply
For Each c In Selection
lenText = Len(c.Text)
stText = stText + 1
With ResCell.Characters(stText, lenText).Font
Select Case c.Value
Case Is < 3
.Bold = True
.Color = vbBlue
Case Is > 15
.Bold = False
.Color = vbRed
Case Else
.Bold = False
.Color = vbBlack
End Select
End With
stText = stText + lenText + Len(sep) - 1
Next c
End Sub
========================================

If necessary, it is possible to, within the Sub, detect the conditional formats
that apply to each cell and then construct the appropriate routines to do the
formatting (see HELP for FormatConditions).

Also, when you write "I have excel round ..." is this done with a ROUND formula
within the cell, or is it done via formatting? If it's done with ROUND
formula, then the ROUND function in the macro is superfluous. If it is done
with cell formatting, then be aware that values which ROUND to your break
points will display based on the unrounded value.

Hope this gets you started.

--ron

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 <[email protected]>,
> Sandwiches2 <[email protected]> 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!
>