| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 Different Font Colors in 1 Cell
Hello everyone, I created a drop down list (Open, Update, Closed) and it
works fine, my question is, can I program the font color "in the same cell" as 2 different colors? eg.: (State: In Black) (Open In Blue) State: Open ="State: "&IF('Alert Setup'!$C$6="","",'Alert Setup'!$C$6) PS: I need this so i can copy and paste this into an email with no cell coding |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
No, you can only have different text formatting in a cell if that cell contains text constants... you have a formula, so the entire cell will be formatted the same.
-- Rick (MVP - Excel) "Joe Wildman" <Joe wrote in message ... Hello everyone, I created a drop down list (Open, Update, Closed) and it works fine, my question is, can I program the font color "in the same cell" as 2 different colors? eg.: (State: In Black) (Open In Blue) State: Open ="State: "&IF('Alert Setup'!$C$6="","",'Alert Setup'!$C$6) PS: I need this so i can copy and paste this into an email with no cell coding |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
Hi Joe,
Use conditional formatting on the cell. In XL2003 FormatConditional Formatting; in Xl2007 its on the ribbon in the Styles grouping. Have a look at the first video on this link http://www.contextures.com/xlVideos0...#CondFormatRow if you need further information. Regards Paul "Joe Wildman" <Joe wrote in message ... Hello everyone, I created a drop down list (Open, Update, Closed) and it works fine, my question is, can I program the font color "in the same cell" as 2 different colors? eg.: (State: In Black) (Open In Blue) State: Open ="State: "&IF('Alert Setup'!$C$6="","",'Alert Setup'!$C$6) PS: I need this so i can copy and paste this into an email with no cell coding |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
The OP asked "can I program the font color "in the same cell" as 2 different colors" on text that is the result of a formula... how will Conditional Formatting allow him to do that?
-- Rick (MVP - Excel) "Paul Wilson" wrote in message ... Hi Joe, Use conditional formatting on the cell. In XL2003 FormatConditional Formatting; in Xl2007 its on the ribbon in the Styles grouping. Have a look at the first video on this link http://www.contextures.com/xlVideos0...#CondFormatRow if you need further information. Regards Paul "Joe Wildman" <Joe wrote in message ... Hello everyone, I created a drop down list (Open, Update, Closed) and it works fine, my question is, can I program the font color "in the same cell" as 2 different colors? eg.: (State: In Black) (Open In Blue) State: Open ="State: "&IF('Alert Setup'!$C$6="","",'Alert Setup'!$C$6) PS: I need this so i can copy and paste this into an email with no cell coding |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
Hi,
It can't. The only ways you can do this are with a linked picture of two adjacent cells place in another cell. And that is more trouble than it's worth and/but it doesn't require conditional formatting. Or in 2007 by linking a shape to the cell with a formula and then manually controlling the gradient fill of the text. Again far more work than it's worth in MHO. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Rick Rothstein" wrote: The OP asked "can I program the font color "in the same cell" as 2 different colors" on text that is the result of a formula... how will Conditional Formatting allow him to do that? -- Rick (MVP - Excel) "Paul Wilson" wrote in message ... Hi Joe, Use conditional formatting on the cell. In XL2003 FormatConditional Formatting; in Xl2007 its on the ribbon in the Styles grouping. Have a look at the first video on this link http://www.contextures.com/xlVideos0...#CondFormatRow if you need further information. Regards Paul "Joe Wildman" <Joe wrote in message ... Hello everyone, I created a drop down list (Open, Update, Closed) and it works fine, my question is, can I program the font color "in the same cell" as 2 different colors? eg.: (State: In Black) (Open In Blue) State: Open ="State: "&IF('Alert Setup'!$C$6="","",'Alert Setup'!$C$6) PS: I need this so i can copy and paste this into an email with no cell coding |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
Actually, that was sort of a rhetorical question.<g
Anyway, linked pictures are not the only way to do this... you can use worksheet event code to do it as well. Assuming D6 is the cell slated to display the 2-color text, the OP could leave the cell blank (that is, not put a formula in it) and use this Worksheet Change event code instead... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$6" Then With Range("D6") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Target.Value .Characters(1, 6).Font.ColorIndex = 1 .Characters(8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub To implement this solution, the OP would need to right click the Tab at the bottom of the worksheet, select View Code from the popup menu that appears and then copy/paste the above into the code window that appears. From then on, changing the value of the C6 via his drop down or via a key in would kick off the event code placing the desired text into D6 in the two colors he indicated he wanted. -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, It can't. The only ways you can do this are with a linked picture of two adjacent cells place in another cell. And that is more trouble than it's worth and/but it doesn't require conditional formatting. Or in 2007 by linking a shape to the cell with a formula and then manually controlling the gradient fill of the text. Again far more work than it's worth in MHO. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Rick Rothstein" wrote: The OP asked "can I program the font color "in the same cell" as 2 different colors" on text that is the result of a formula... how will Conditional Formatting allow him to do that? -- Rick (MVP - Excel) "Paul Wilson" wrote in message ... Hi Joe, Use conditional formatting on the cell. In XL2003 FormatConditional Formatting; in Xl2007 its on the ribbon in the Styles grouping. Have a look at the first video on this link http://www.contextures.com/xlVideos0...#CondFormatRow if you need further information. Regards Paul "Joe Wildman" <Joe wrote in message ... Hello everyone, I created a drop down list (Open, Update, Closed) and it works fine, my question is, can I program the font color "in the same cell" as 2 different colors? eg.: (State: In Black) (Open In Blue) State: Open ="State: "&IF('Alert Setup'!$C$6="","",'Alert Setup'!$C$6) PS: I need this so i can copy and paste this into an email with no cell coding |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
Rick... you are... the MAN! WOW thanks a lot, BAM! I thought i was asking for
something that didn't exist! "Rick Rothstein" wrote: Actually, that was sort of a rhetorical question.<g Anyway, linked pictures are not the only way to do this... you can use worksheet event code to do it as well. Assuming D6 is the cell slated to display the 2-color text, the OP could leave the cell blank (that is, not put a formula in it) and use this Worksheet Change event code instead... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$6" Then With Range("D6") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Target.Value .Characters(1, 6).Font.ColorIndex = 1 .Characters(8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub To implement this solution, the OP would need to right click the Tab at the bottom of the worksheet, select View Code from the popup menu that appears and then copy/paste the above into the code window that appears. From then on, changing the value of the C6 via his drop down or via a key in would kick off the event code placing the desired text into D6 in the two colors he indicated he wanted. -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, It can't. The only ways you can do this are with a linked picture of two adjacent cells place in another cell. And that is more trouble than it's worth and/but it doesn't require conditional formatting. Or in 2007 by linking a shape to the cell with a formula and then manually controlling the gradient fill of the text. Again far more work than it's worth in MHO. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Rick Rothstein" wrote: The OP asked "can I program the font color "in the same cell" as 2 different colors" on text that is the result of a formula... how will Conditional Formatting allow him to do that? -- Rick (MVP - Excel) "Paul Wilson" wrote in message ... Hi Joe, Use conditional formatting on the cell. In XL2003 FormatConditional Formatting; in Xl2007 its on the ribbon in the Styles grouping. Have a look at the first video on this link http://www.contextures.com/xlVideos0...#CondFormatRow if you need further information. Regards Paul "Joe Wildman" <Joe wrote in message ... Hello everyone, I created a drop down list (Open, Update, Closed) and it works fine, my question is, can I program the font color "in the same cell" as 2 different colors? eg.: (State: In Black) (Open In Blue) State: Open ="State: "&IF('Alert Setup'!$C$6="","",'Alert Setup'!$C$6) PS: I need this so i can copy and paste this into an email with no cell coding |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
Actually, my comment was just supporting your implied answer.
And the answer still remains No. Because in my example one is using a picture, in your example the formula is gone. The upside of the picture is it is dynamic, while the VBA solution requires the user to modify the macro everytime they want to change formats. The upside to the VBA solutions is it just requires a little code which can be copied, will my suggestion requires about 10 steps to set up. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Rick Rothstein" wrote: Actually, that was sort of a rhetorical question.<g Anyway, linked pictures are not the only way to do this... you can use worksheet event code to do it as well. Assuming D6 is the cell slated to display the 2-color text, the OP could leave the cell blank (that is, not put a formula in it) and use this Worksheet Change event code instead... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$6" Then With Range("D6") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Target.Value .Characters(1, 6).Font.ColorIndex = 1 .Characters(8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub To implement this solution, the OP would need to right click the Tab at the bottom of the worksheet, select View Code from the popup menu that appears and then copy/paste the above into the code window that appears. From then on, changing the value of the C6 via his drop down or via a key in would kick off the event code placing the desired text into D6 in the two colors he indicated he wanted. -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, It can't. The only ways you can do this are with a linked picture of two adjacent cells place in another cell. And that is more trouble than it's worth and/but it doesn't require conditional formatting. Or in 2007 by linking a shape to the cell with a formula and then manually controlling the gradient fill of the text. Again far more work than it's worth in MHO. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Rick Rothstein" wrote: The OP asked "can I program the font color "in the same cell" as 2 different colors" on text that is the result of a formula... how will Conditional Formatting allow him to do that? -- Rick (MVP - Excel) "Paul Wilson" wrote in message ... Hi Joe, Use conditional formatting on the cell. In XL2003 FormatConditional Formatting; in Xl2007 its on the ribbon in the Styles grouping. Have a look at the first video on this link http://www.contextures.com/xlVideos0...#CondFormatRow if you need further information. Regards Paul "Joe Wildman" <Joe wrote in message ... Hello everyone, I created a drop down list (Open, Update, Closed) and it works fine, my question is, can I program the font color "in the same cell" as 2 different colors? eg.: (State: In Black) (Open In Blue) State: Open ="State: "&IF('Alert Setup'!$C$6="","",'Alert Setup'!$C$6) PS: I need this so i can copy and paste this into an email with no cell coding |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
See inline comments...
Actually, my comment was just supporting your implied answer. Ah, I see (it is not always easy to pick up on intent with verbal clues which, of course, are non-existent in a printed message). And the answer still remains No. Because in my example one is using a picture, in your example the formula is gone. Correct... I completely changed the OP's formula approach (although, in essence, a formula is a kind of event code... something changes in response to user action... so basically I traded one form of event handling with another). The upside of the picture is it is dynamic, while the VBA solution requires the user to modify the macro everytime they want to change formats. Actually, I'm not familiar with the picture method you are referring to; but I would guess if the user wanted to change formats using your method, they would still have to change something (the picture, perhaps?). My VB method does not have to be as rigid as your message might be implying... it could be made more flexible. For example, the color codes could be held in cells on a worksheet somewhere and these cells could be referenced in code rather than having the color values hardcoded in the code itself. That way, the user could change the colors of the parts quite simply without having to touch the code. And, of course, the code could be made more flexible yet, depending on the parameters of the problem being addressed (for example, if the first word were not always "State:", then the code could be modified easily enough to grab the text in front of the first space, and so on). Just like the formula needs to be crafted to solve the ultimate problem being addressed, the VB code needs to be crafted to solve the ultimate problem as well... and it can pretty much be made as friendly as required. The upside to the VBA solutions is it just requires a little code which can be copied, will my suggestion requires about 10 steps to set up. As I said, I am not familiar with the "picture method"... any chance you could post the steps involved so I (and others) can see how it works? -- Rick (MVP - Excel) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
OK Rick one more and i hope this is the last question...
I need to create another line, just a simple text input with the same format on a second line, I am unable to get "Issue: to show up on the second line as shown below. I was unable to get any functions to make that happen eg. Status: Open Issue: Input Text Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$6" Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Target.Value .Characters(1, 6).Font.ColorIndex = 1 .Characters(8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_Change2(ByVal Target As Range) If Target.Address = "$C$7" Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "Issue: " & Target.Value .Characters(1, 6).Font.ColorIndex = 1 .Characters(8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub __________________________________________________ _____ Coding in here is kinda new to me, I was able to "Rick Rothstein" wrote: See inline comments... Actually, my comment was just supporting your implied answer. Ah, I see (it is not always easy to pick up on intent with verbal clues which, of course, are non-existent in a printed message). And the answer still remains No. Because in my example one is using a picture, in your example the formula is gone. Correct... I completely changed the OP's formula approach (although, in essence, a formula is a kind of event code... something changes in response to user action... so basically I traded one form of event handling with another). The upside of the picture is it is dynamic, while the VBA solution requires the user to modify the macro everytime they want to change formats. Actually, I'm not familiar with the picture method you are referring to; but I would guess if the user wanted to change formats using your method, they would still have to change something (the picture, perhaps?). My VB method does not have to be as rigid as your message might be implying... it could be made more flexible. For example, the color codes could be held in cells on a worksheet somewhere and these cells could be referenced in code rather than having the color values hardcoded in the code itself. That way, the user could change the colors of the parts quite simply without having to touch the code. And, of course, the code could be made more flexible yet, depending on the parameters of the problem being addressed (for example, if the first word were not always "State:", then the code could be modified easily enough to grab the text in front of the first space, and so on). Just like the formula needs to be crafted to solve the ultimate problem being addressed, the VB code needs to be crafted to solve the ultimate problem as well... and it can pretty much be made as friendly as required. The upside to the VBA solutions is it just requires a little code which can be copied, will my suggestion requires about 10 steps to set up. As I said, I am not familiar with the "picture method"... any chance you could post the steps involved so I (and others) can see how it works? -- Rick (MVP - Excel) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
Rick sorry, i forgot to tell you that both,
Status: Issue: needs to be in the same cell. "Joe Wildman" wrote: OK Rick one more and i hope this is the last question... I need to create another line, just a simple text input with the same format on a second line, I am unable to get "Issue: to show up on the second line as shown below. I was unable to get any functions to make that happen eg. Status: Open Issue: Input Text Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$6" Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Target.Value .Characters(1, 6).Font.ColorIndex = 1 .Characters(8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_Change2(ByVal Target As Range) If Target.Address = "$C$7" Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "Issue: " & Target.Value .Characters(1, 6).Font.ColorIndex = 1 .Characters(8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub __________________________________________________ _____ Coding in here is kinda new to me, I was able to "Rick Rothstein" wrote: See inline comments... Actually, my comment was just supporting your implied answer. Ah, I see (it is not always easy to pick up on intent with verbal clues which, of course, are non-existent in a printed message). And the answer still remains No. Because in my example one is using a picture, in your example the formula is gone. Correct... I completely changed the OP's formula approach (although, in essence, a formula is a kind of event code... something changes in response to user action... so basically I traded one form of event handling with another). The upside of the picture is it is dynamic, while the VBA solution requires the user to modify the macro everytime they want to change formats. Actually, I'm not familiar with the picture method you are referring to; but I would guess if the user wanted to change formats using your method, they would still have to change something (the picture, perhaps?). My VB method does not have to be as rigid as your message might be implying... it could be made more flexible. For example, the color codes could be held in cells on a worksheet somewhere and these cells could be referenced in code rather than having the color values hardcoded in the code itself. That way, the user could change the colors of the parts quite simply without having to touch the code. And, of course, the code could be made more flexible yet, depending on the parameters of the problem being addressed (for example, if the first word were not always "State:", then the code could be modified easily enough to grab the text in front of the first space, and so on). Just like the formula needs to be crafted to solve the ultimate problem being addressed, the VB code needs to be crafted to solve the ultimate problem as well... and it can pretty much be made as friendly as required. The upside to the VBA solutions is it just requires a little code which can be copied, will my suggestion requires about 10 steps to set up. As I said, I am not familiar with the "picture method"... any chance you could post the steps involved so I (and others) can see how it works? -- Rick (MVP - Excel) |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
Unlike macros, you cannot just name event procedures with any names you want... they are fixed by VB and there is only one event procedure per type of event. That means all the code needs to be in the same procedure and your 2-line requirement requires a slightly different approach (which is why it is almost **never** a good idea to simply your requirements when posting questions on newsgroups). Try this code in place of what you have now and see if it does what you want...
Private Sub Worksheet_Change(ByVal Target As Range) Dim LineFeed As Long If Not Intersect(Target, Range("C6:C7")) Is Nothing Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Range("C6").Value & vbLf & _ "Issue: " & Range("C7").Value LineFeed = InStr(.Value, vbLf) .Characters(1, 6).Font.ColorIndex = 1 .Characters(8, LineFeed - 6).Font.ColorIndex = 5 .Characters(LineFeed + 1, 6).Font.ColorIndex = 1 .Characters(LineFeed + 8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub -- Rick (MVP - Excel) "Joe Wildman" wrote in message ... Rick sorry, i forgot to tell you that both, Status: Issue: needs to be in the same cell. "Joe Wildman" wrote: OK Rick one more and i hope this is the last question... I need to create another line, just a simple text input with the same format on a second line, I am unable to get "Issue: to show up on the second line as shown below. I was unable to get any functions to make that happen eg. Status: Open Issue: Input Text Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$6" Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Target.Value .Characters(1, 6).Font.ColorIndex = 1 .Characters(8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_Change2(ByVal Target As Range) If Target.Address = "$C$7" Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "Issue: " & Target.Value .Characters(1, 6).Font.ColorIndex = 1 .Characters(8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub __________________________________________________ _____ Coding in here is kinda new to me, I was able to "Rick Rothstein" wrote: See inline comments... Actually, my comment was just supporting your implied answer. Ah, I see (it is not always easy to pick up on intent with verbal clues which, of course, are non-existent in a printed message). And the answer still remains No. Because in my example one is using a picture, in your example the formula is gone. Correct... I completely changed the OP's formula approach (although, in essence, a formula is a kind of event code... something changes in response to user action... so basically I traded one form of event handling with another). The upside of the picture is it is dynamic, while the VBA solution requires the user to modify the macro everytime they want to change formats. Actually, I'm not familiar with the picture method you are referring to; but I would guess if the user wanted to change formats using your method, they would still have to change something (the picture, perhaps?). My VB method does not have to be as rigid as your message might be implying... it could be made more flexible. For example, the color codes could be held in cells on a worksheet somewhere and these cells could be referenced in code rather than having the color values hardcoded in the code itself. That way, the user could change the colors of the parts quite simply without having to touch the code. And, of course, the code could be made more flexible yet, depending on the parameters of the problem being addressed (for example, if the first word were not always "State:", then the code could be modified easily enough to grab the text in front of the first space, and so on). Just like the formula needs to be crafted to solve the ultimate problem being addressed, the VB code needs to be crafted to solve the ultimate problem as well... and it can pretty much be made as friendly as required. The upside to the VBA solutions is it just requires a little code which can be copied, will my suggestion requires about 10 steps to set up. As I said, I am not familiar with the "picture method"... any chance you could post the steps involved so I (and others) can see how it works? -- Rick (MVP - Excel) |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
Works great but for the life of me, been working on this for 4 hours can't
get the color coding right, i am unable edit the LineFeed = InStr(.Value, vbLf) .Characters(1, 6).Font.ColorIndex = 1.. If i can get this working then I am all done, again thanks a lot for your help Rick :) Private Sub Worksheet_Change(ByVal Target As Range) Dim LineFeed As Long If Not Intersect(Target, Range("C6:C7:C8:C9:C10:C11:C12:C13:C14:C15:C16")) Is Nothing Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Range("C6").Value & vbLf & _ "Issue: " & Range("C7").Value & vbLf & _ "Date/Time: " & Range("C8").Value & vbLf & _ "Platform: " & Range("C9").Value & vbLf & _ "Ticket: " & Range("C10").Value & vbLf & _ "Ajusted Severity: " & Range("C11").Value & vbLf & _ "Front end message: " & Range("C12").Value & vbLf & _ "Affects: " & Range("C13").Value & vbLf & _ "ETR: " & Range("C14").Value & vbLf & _ "Action: " & Range("C15").Value & vbLf & _ "Per: " & Range("C16").Value LineFeed = InStr(.Value, vbLf) .Characters(1, 6).Font.ColorIndex = 1 .Characters(8, LineFeed - 6).Font.ColorIndex = 5 .Characters(LineFeed + 1, 6).Font.ColorIndex = 1 .Characters(LineFeed + 11).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub "Rick Rothstein" wrote: Unlike macros, you cannot just name event procedures with any names you want... they are fixed by VB and there is only one event procedure per type of event. That means all the code needs to be in the same procedure and your 2-line requirement requires a slightly different approach (which is why it is almost **never** a good idea to simply your requirements when posting questions on newsgroups). Try this code in place of what you have now and see if it does what you want... Private Sub Worksheet_Change(ByVal Target As Range) Dim LineFeed As Long If Not Intersect(Target, Range("C6:C7")) Is Nothing Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Range("C6").Value & vbLf & _ "Issue: " & Range("C7").Value LineFeed = InStr(.Value, vbLf) .Characters(1, 6).Font.ColorIndex = 1 .Characters(8, LineFeed - 6).Font.ColorIndex = 5 .Characters(LineFeed + 1, 6).Font.ColorIndex = 1 .Characters(LineFeed + 8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub -- Rick (MVP - Excel) "Joe Wildman" wrote in message ... Rick sorry, i forgot to tell you that both, Status: Issue: needs to be in the same cell. "Joe Wildman" wrote: OK Rick one more and i hope this is the last question... I need to create another line, just a simple text input with the same format on a second line, I am unable to get "Issue: to show up on the second line as shown below. I was unable to get any functions to make that happen eg. Status: Open Issue: Input Text Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$6" Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Target.Value .Characters(1, 6).Font.ColorIndex = 1 .Characters(8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_Change2(ByVal Target As Range) If Target.Address = "$C$7" Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "Issue: " & Target.Value .Characters(1, 6).Font.ColorIndex = 1 .Characters(8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub __________________________________________________ _____ Coding in here is kinda new to me, I was able to "Rick Rothstein" wrote: See inline comments... Actually, my comment was just supporting your implied answer. Ah, I see (it is not always easy to pick up on intent with verbal clues which, of course, are non-existent in a printed message). And the answer still remains No. Because in my example one is using a picture, in your example the formula is gone. Correct... I completely changed the OP's formula approach (although, in essence, a formula is a kind of event code... something changes in response to user action... so basically I traded one form of event handling with another). The upside of the picture is it is dynamic, while the VBA solution requires the user to modify the macro everytime they want to change formats. Actually, I'm not familiar with the picture method you are referring to; but I would guess if the user wanted to change formats using your method, they would still have to change something (the picture, perhaps?). My VB method does not have to be as rigid as your message might be implying... it could be made more flexible. For example, the color codes could be held in cells on a worksheet somewhere and these cells could be referenced in code rather than having the color values hardcoded in the code itself. That way, the user could change the colors of the parts quite simply without having to touch the code. And, of course, the code could be made more flexible yet, depending on the parameters of the problem being addressed (for example, if the first word were not always "State:", then the code could be modified easily enough to grab the text in front of the first space, and so on). Just like the formula needs to be crafted to solve the ultimate problem being addressed, the VB code needs to be crafted to solve the ultimate problem as well... and it can pretty much be made as friendly as required. The upside to the VBA solutions is it just requires a little code which can be copied, will my suggestion requires about 10 steps to set up. As I said, I am not familiar with the "picture method"... any chance you could post the steps involved so I (and others) can see how it works? -- Rick (MVP - Excel) |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
Here is yet another perfect example of why simplified examples should not be used when asking for help on these newsgroups. Code, like worksheet formulas, are crafted around the conditions they have to work in... just like there is not one formula to solve all similar problems, there is not one coding approach that can be universally applied to all similar looking problems.
The condition you now are asking about requires a completely different approach from either of the solutions used for the first and second examples you posted. I believe this code should do what you want... Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long Dim Colon As Long Dim LineFeed As Long Dim LineStart As Long If Not Intersect(Target, Range("C6:C16")) Is Nothing Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Range("C6").Value & vbLf & _ "Issue: " & Range("C7").Value & vbLf & _ "Date/Time: " & Range("C8").Value & vbLf & _ "Platform: " & Range("C9").Value & vbLf & _ "Ticket: " & Range("C10").Value & vbLf & _ "Ajusted Severity: " & Range("C11").Value & vbLf & _ "Front end message: " & Range("C12").Value & vbLf & _ "Affects: " & Range("C13").Value & vbLf & _ "ETR: " & Range("C14").Value & vbLf & _ "Action: " & Range("C15").Value & vbLf & _ "Per: " & Range("C16").Value .Font.ColorIndex = 0 LineStart = 1 Do Colon = InStr(LineStart, .Value, ":") LineFeed = InStr(LineStart, .Value & vbLf, vbLf) .Characters(LineStart, Colon - LineStart + 1).Font.ColorIndex = 1 .Characters(Colon + 1, LineFeed - Colon + 2).Font.ColorIndex = 5 LineStart = LineFeed + 1 Loop While LineFeed < Len(.Value) End With End If Whoops: Application.EnableEvents = True End Sub -- Rick (MVP - Excel) "Joe Wildman" wrote in message ... Works great but for the life of me, been working on this for 4 hours can't get the color coding right, i am unable edit the LineFeed = InStr(.Value, vbLf) .Characters(1, 6).Font.ColorIndex = 1.. If i can get this working then I am all done, again thanks a lot for your help Rick :) Private Sub Worksheet_Change(ByVal Target As Range) Dim LineFeed As Long If Not Intersect(Target, Range("C6:C7:C8:C9:C10:C11:C12:C13:C14:C15:C16")) Is Nothing Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Range("C6").Value & vbLf & _ "Issue: " & Range("C7").Value & vbLf & _ "Date/Time: " & Range("C8").Value & vbLf & _ "Platform: " & Range("C9").Value & vbLf & _ "Ticket: " & Range("C10").Value & vbLf & _ "Ajusted Severity: " & Range("C11").Value & vbLf & _ "Front end message: " & Range("C12").Value & vbLf & _ "Affects: " & Range("C13").Value & vbLf & _ "ETR: " & Range("C14").Value & vbLf & _ "Action: " & Range("C15").Value & vbLf & _ "Per: " & Range("C16").Value LineFeed = InStr(.Value, vbLf) .Characters(1, 6).Font.ColorIndex = 1 .Characters(8, LineFeed - 6).Font.ColorIndex = 5 .Characters(LineFeed + 1, 6).Font.ColorIndex = 1 .Characters(LineFeed + 11).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub "Rick Rothstein" wrote: Unlike macros, you cannot just name event procedures with any names you want... they are fixed by VB and there is only one event procedure per type of event. That means all the code needs to be in the same procedure and your 2-line requirement requires a slightly different approach (which is why it is almost **never** a good idea to simply your requirements when posting questions on newsgroups). Try this code in place of what you have now and see if it does what you want... Private Sub Worksheet_Change(ByVal Target As Range) Dim LineFeed As Long If Not Intersect(Target, Range("C6:C7")) Is Nothing Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Range("C6").Value & vbLf & _ "Issue: " & Range("C7").Value LineFeed = InStr(.Value, vbLf) .Characters(1, 6).Font.ColorIndex = 1 .Characters(8, LineFeed - 6).Font.ColorIndex = 5 .Characters(LineFeed + 1, 6).Font.ColorIndex = 1 .Characters(LineFeed + 8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub -- Rick (MVP - Excel) "Joe Wildman" wrote in message ... Rick sorry, i forgot to tell you that both, Status: Issue: needs to be in the same cell. "Joe Wildman" wrote: OK Rick one more and i hope this is the last question... I need to create another line, just a simple text input with the same format on a second line, I am unable to get "Issue: to show up on the second line as shown below. I was unable to get any functions to make that happen eg. Status: Open Issue: Input Text Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$6" Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "State: " & Target.Value .Characters(1, 6).Font.ColorIndex = 1 .Characters(8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_Change2(ByVal Target As Range) If Target.Address = "$C$7" Then With Range("A50") On Error GoTo Whoops Application.EnableEvents = False .Value = "Issue: " & Target.Value .Characters(1, 6).Font.ColorIndex = 1 .Characters(8).Font.ColorIndex = 5 End With End If Whoops: Application.EnableEvents = True End Sub __________________________________________________ _____ Coding in here is kinda new to me, I was able to "Rick Rothstein" wrote: See inline comments... Actually, my comment was just supporting your implied answer. Ah, I see (it is not always easy to pick up on intent with verbal clues which, of course, are non-existent in a printed message). And the answer still remains No. Because in my example one is using a picture, in your example the formula is gone. Correct... I completely changed the OP's formula approach (although, in essence, a formula is a kind of event code... something changes in response to user action... so basically I traded one form of event handling with another). The upside of the picture is it is dynamic, while the VBA solution requires the user to modify the macro everytime they want to change formats. Actually, I'm not familiar with the picture method you are referring to; but I would guess if the user wanted to change formats using your method, they would still have to change something (the picture, perhaps?). My VB method does not have to be as rigid as your message might be implying... it could be made more flexible. For example, the color codes could be held in cells on a worksheet somewhere and these cells could be referenced in code rather than having the color values hardcoded in the code itself. That way, the user could change the colors of the parts quite simply without having to touch the code. And, of course, the code could be made more flexible yet, depending on the parameters of the problem being addressed (for example, if the first word were not always "State:", then the code could be modified easily enough to grab the text in front of the first space, and so on). Just like the formula needs to be crafted to solve the ultimate problem being addressed, the VB code needs to be crafted to solve the ultimate problem as well... and it can pretty much be made as friendly as required. The upside to the VBA solutions is it just requires a little code which can be copied, will my suggestion requires about 10 steps to set up. As I said, I am not familiar with the "picture method"... any chance you could post the steps involved so I (and others) can see how it works? -- Rick (MVP - Excel) |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
|
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 Different Font Colors in 1 Cell
|
|
#17
|
|||
|
|||
|
You can do this using conditional formatting
Please follow following steps 1.Select any cell in sheet1 (say A2) 2.Click FormatConditional Formatting from the toolbar 3.Select cell value is from the leftmost drop down list 4.Select equals to from the middle drop down list 5.Enter State in the box to the right 6.Click on Format button 7.From the font tab select font color as Black 8.Click Add button 9. Select cell value is from the leftmost drop down list 10. Select equals to from the middle drop down list 11. .Enter Open in the box to the right 12. Click on Format button 13. From the font tab select font color as Blue 14.Click OK Similarly you can apply this to the whole column by selecting whole column and following above steps Hope you got it! Have a nice time…… Chris ------ Convert your Excel spreadsheet into an online calculator. http://www.spreadsheetconverter.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell with two colors for the font | Excel Discussion (Misc queries) | |||
| Lost highlighting and font colors; background colors on web pages | Excel Discussion (Misc queries) | |||
| Font & Cell colors are not showing up on my monitor in Excel. | Setting up and Configuration of Excel | |||
| Format a cell to recognize data and change font colors accordingly | Excel Worksheet Functions | |||
| Can't format font colors or cell fill-in colors | Excel Discussion (Misc queries) | |||
Linear Mode
