2 Different Font Colors in 1 Cell

[Joe Wildman]

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

 

[Rick Rothstein]

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.

 

[Paul Wilson]

Hi Joe,

Use conditional formatting on the cell. In XL2003 Format>Conditional
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/xlVideos06.html#CondFormatRow

if you need further information.

Regards

Paul

 

[Rick Rothstein]

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?

 

[Shane Devenshire]

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.

 

[Rick Rothstein]

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.

 

[Joe Wildman]

Rick... you are... the MAN! WOW thanks a lot, BAM! I thought i was asking for
something that didn't exist!

 

[Shane Devenshire]

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.

 

[Rick Rothstein]

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?

 

[Joe Wildman]

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

See inline comments...


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).


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).


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.

 

[Joe Wildman]

Rick sorry, i forgot to tell you that both,

Status:
Issue:

needs to be in the same cell.

 

[Rick Rothstein]

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

 

[Joe Wildman]

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]

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)

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

 

[Joe Wildman]

This was a bit over my head, what we created today will be used 40 times a
week and will take a lot of stress out of my life. If you need someone for
something I will be more than happy to volunteer, thanks Rick

Email: (e-mail address removed)

 

[Rick Rothstein]

I'm glad we were able to get everything worked out for you.