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)