Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
concatenate formatted cells in excel
=CONCATENATE(A1,B1,C1) I have never heard of having multiple colors in one cell you can also use =A1&" "&B1&" "&C1 -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=522024 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
concatenate formatted cells in excel
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
concatenate formatted cells in excel
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
concatenate formatted cells in excel
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I concatenate custom formatted numbers | Excel Worksheet Functions | |||
how do I concatenate custom formatted numbers | Excel Worksheet Functions | |||
how do i concatenate conditionally formatted cells in excel | Excel Worksheet Functions | |||
How do I save formatted cells in excel? | Excel Discussion (Misc queries) | |||
Converting 'General' formatted cells to Text formatted cell using. | Excel Worksheet Functions |