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

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