Home |
Search |
Today's Posts |
#1
|
|||
|
|||
FONT COLOR FORMAT
WHEN I COMBINE TEXT ( EX. =A1&B1&C1) WITH DIFFERENT FONT COLORS, IT RETURNS
THE COMBINED TEXT IN A SINGLE COLOR FONT. HOW DO I CORRECT THIS SO THE TEXT IS RETURNED (COMBINED) IN THE ORIGINAL FONT COLOR FORMAT FROM THE ORIGINAL INDIVIDUAL CELLS (A1 B1 C1)? |
#2
|
|||
|
|||
Hi
Excel doesn't allow partial formatting of cells and hence will return the default format. You may be able to do it with VBA, but I don't think so. Oh, BTW, please don't post in capitals as it is considered to be shouting as well as being really hard to read. HTH Michael Mitchelson "JMCA2000" wrote: WHEN I COMBINE TEXT ( EX. =A1&B1&C1) WITH DIFFERENT FONT COLORS, IT RETURNS THE COMBINED TEXT IN A SINGLE COLOR FONT. HOW DO I CORRECT THIS SO THE TEXT IS RETURNED (COMBINED) IN THE ORIGINAL FONT COLOR FORMAT FROM THE ORIGINAL INDIVIDUAL CELLS (A1 B1 C1)? |
#3
|
|||
|
|||
A formula cannot be used to modifiy the format of a cell, it just returns a
value. Excel (2002/2003) will allow you to part format the font of a cell if it contains text but not if it contains a formula. So if you want your result to be dynamic and keep the formula in the cell I don't believe you can have different font colours. If you are happy to replace the formula with its result in text format then you could do it with a macro like this (select the cell containing the formula first): Sub FText() Dim Prec As Range Dim cell As Range Dim TLen As Integer Set Prec = ActiveCell.DirectPrecedents TLen = 1 With ActiveCell .NumberFormat = "@" .Value = .Value End With For Each cell In Prec ActiveCell.Characters(TLen, Len(cell.Text)).Font.ColorIndex = _ cell.Font.ColorIndex TLen = TLen + Len(cell.Text) Next cell End Sub Hope this helps Rowan "JMCA2000" wrote: WHEN I COMBINE TEXT ( EX. =A1&B1&C1) WITH DIFFERENT FONT COLORS, IT RETURNS THE COMBINED TEXT IN A SINGLE COLOR FONT. HOW DO I CORRECT THIS SO THE TEXT IS RETURNED (COMBINED) IN THE ORIGINAL FONT COLOR FORMAT FROM THE ORIGINAL INDIVIDUAL CELLS (A1 B1 C1)? |
#4
|
|||
|
|||
JM, This will Format partial text in a cell. You may be able to change it for your application. And put a target in instead of active cell. Sub format() With ActiveCell.Characters(Start:=4, Length:=3).Font .FontStyle = "Bold" .FontStyle = "Regular" .Color = vbRed .Name = "Arial" .Size = 10 .ColorIndex = 1 End With End Sub "Start" is how many characters from the left you want the formating to start. "Length" is how many characters you want the formating to continue. JMCA2000 Wrote: WHEN I COMBINE TEXT ( EX. =A1&B1&C1) WITH DIFFERENT FONT COLORS, IT RETURNS THE COMBINED TEXT IN A SINGLE COLOR FONT. HOW DO I CORRECT THIS SO THE TEXT IS RETURNED (COMBINED) IN THE ORIGINAL FONT COLOR FORMAT FROM THE ORIGINAL INDIVIDUAL CELLS (A1 B1 C1)? -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=397654 |
#5
|
|||
|
|||
Thank You for your support!!!
I have no problem using another column for the formula and then referencing another column for the results. I am not sure what you are suggesting or how to do this. I did not find a function in the Excel help called Sub format() and I don't know how to create this type of Macro with the Macro recorder. I would Greatly Appreciate your further support with this issue. For Your Information: The text from any one of the original columns row(same row) (A1 B1 C1, A2 B2 C2, A3 B3 C3, etc.) is only one letter (O Blank Blank, Blank S Blank, Blank Blank R, etc.) and the result in the reference column will only contain one letter from each of the original columns row (A1 B1 C1, etc.). The reason why I need the different color font letters is because the results will be a continuous string of single letters in the resulting column and the different color letters will tell me what was the original column that it came from when scrolling down. Thank You Again For Your Continually Support!!! "Piranha" wrote: JM, This will Format partial text in a cell. You may be able to change it for your application. And put a target in instead of active cell. Sub format() With ActiveCell.Characters(Start:=4, Length:=3).Font .FontStyle = "Bold" .FontStyle = "Regular" .Color = vbRed .Name = "Arial" .Size = 10 .ColorIndex = 1 End With End Sub "Start" is how many characters from the left you want the formating to start. "Length" is how many characters you want the formating to continue. JMCA2000 Wrote: WHEN I COMBINE TEXT ( EX. =A1&B1&C1) WITH DIFFERENT FONT COLORS, IT RETURNS THE COMBINED TEXT IN A SINGLE COLOR FONT. HOW DO I CORRECT THIS SO THE TEXT IS RETURNED (COMBINED) IN THE ORIGINAL FONT COLOR FORMAT FROM THE ORIGINAL INDIVIDUAL CELLS (A1 B1 C1)? -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=397654 |
#6
|
|||
|
|||
Thank You for your support!!!
I have no problem using another column for the formula and then referencing another column for the results. I am not sure what you are suggesting or how to do this. I did not find a function in the Excel help called Sub FText() and I don't know how to create this type of Macro with the Macro recorder. I would Greatly Appreciate your further support with this issue. For Your Information: The text from any one of the original columns row(same row) (A1 B1 C1, A2 B2 C2, A3 B3 C3, etc.) is only one letter (O Blank Blank, Blank S Blank, Blank Blank R, etc.) and the result in the reference column will only contain one letter from each of the original columns row (A1 B1 C1, etc.). The reason why I need the different color font letters is because the results will be a continuous string of single letters in the resulting column and the different color letters will tell me what was the original column that it came from when scrolling down. Thank You Again For Your Continually Support!!! "Michael" wrote: Hi Excel doesn't allow partial formatting of cells and hence will return the default format. You may be able to do it with VBA, but I don't think so. Oh, BTW, please don't post in capitals as it is considered to be shouting as well as being really hard to read. HTH Michael Mitchelson "JMCA2000" wrote: WHEN I COMBINE TEXT ( EX. =A1&B1&C1) WITH DIFFERENT FONT COLORS, IT RETURNS THE COMBINED TEXT IN A SINGLE COLOR FONT. HOW DO I CORRECT THIS SO THE TEXT IS RETURNED (COMBINED) IN THE ORIGINAL FONT COLOR FORMAT FROM THE ORIGINAL INDIVIDUAL CELLS (A1 B1 C1)? |
#7
|
|||
|
|||
Thank You for your support!!!
I have no problem using another column for the formula and then referencing another column for the results. I am not sure what you are suggesting or how to do this. I did not find a function in the Excel help called Sub FText() and I don't know how to create this type of Macro with the Macro recorder. I would Greatly Appreciate your further support with this issue. For Your Information: The text from any one of the original columns row(same row) (A1 B1 C1, A2 B2 C2, A3 B3 C3, etc.) is only one letter (O Blank Blank, Blank S Blank, Blank Blank R, etc.) and the result in the reference column will only contain one letter from each of the original columns row (A1 B1 C1, etc.). The reason why I need the different color font letters is because the results will be a continuous string of single letters in the resulting column and the different color letters will tell me what was the original column that it came from when scrolling down. Thank You Again For Your Continually Support!!! "Rowan" wrote: A formula cannot be used to modifiy the format of a cell, it just returns a value. Excel (2002/2003) will allow you to part format the font of a cell if it contains text but not if it contains a formula. So if you want your result to be dynamic and keep the formula in the cell I don't believe you can have different font colours. If you are happy to replace the formula with its result in text format then you could do it with a macro like this (select the cell containing the formula first): Sub FText() Dim Prec As Range Dim cell As Range Dim TLen As Integer Set Prec = ActiveCell.DirectPrecedents TLen = 1 With ActiveCell .NumberFormat = "@" .Value = .Value End With For Each cell In Prec ActiveCell.Characters(TLen, Len(cell.Text)).Font.ColorIndex = _ cell.Font.ColorIndex TLen = TLen + Len(cell.Text) Next cell End Sub Hope this helps Rowan "JMCA2000" wrote: WHEN I COMBINE TEXT ( EX. =A1&B1&C1) WITH DIFFERENT FONT COLORS, IT RETURNS THE COMBINED TEXT IN A SINGLE COLOR FONT. HOW DO I CORRECT THIS SO THE TEXT IS RETURNED (COMBINED) IN THE ORIGINAL FONT COLOR FORMAT FROM THE ORIGINAL INDIVIDUAL CELLS (A1 B1 C1)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving Excel Charts into Word Documents: font sizes change | Charts and Charting in Excel | |||
Changing default font for copy / paste | Excel Discussion (Misc queries) | |||
Why is first font action v-e-r-y slow? | Excel Discussion (Misc queries) | |||
xl chart font sizing into PPT | Charts and Charting in Excel | |||
How to change the default font and size of "comments"? | Excel Discussion (Misc queries) |