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

--
Rick (MVP - Excel)


"Joe Wildman" wrote in message ...
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:

"Rick Rothstein" wrote:

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)