Automatically change the text format the cell when use a specific text

T

thelordmorgoth

Well let explain in very simple way.

When I input certain words in any cell in a specific range like "ABC" ,"CDE", "EFG", they automatically turn to same the words BUT multi-color. For example:

ABC changes to A(in Red)B(in Green)C(in Blue)

and same for the other words with different colors, I only need 5 words combinations.

I tried the conditional format but it applies the whole cell.

Thank you.
 
C

Claus Busch

Hi,

Am Tue, 12 Mar 2013 13:05:54 -0700 (PDT) schrieb
(e-mail address removed):
When I input certain words in any cell in a specific range like "ABC" ,"CDE", "EFG", they automatically turn to same the words BUT multi-color. For example:

ABC changes to A(in Red)B(in Green)C(in Blue)
Click to expand...

you have to do it with VBA. Folowing code does it for ABC. The other
combinations you can add yourself:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 1 Or Target.Count > 1 Then Exit Sub

Select Case Target.Value
Case "ABC"
Target.Characters(1, 1).Font.ColorIndex = 3
Target.Characters(2, 1).Font.ColorIndex = 4
Target.Characters(3, 1).Font.ColorIndex = 5
End Select

End Sub


Regards
Claus Busch
 
T

thelordmorgoth

Hi,



Am Tue, 12 Mar 2013 13:05:54 -0700 (PDT) schrieb

(e-mail address removed):






you have to do it with VBA. Folowing code does it for ABC. The other

combinations you can add yourself:



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column > 1 Or Target.Count > 1 Then Exit Sub



Select Case Target.Value

Case "ABC"

Target.Characters(1, 1).Font.ColorIndex = 3

Target.Characters(2, 1).Font.ColorIndex = 4

Target.Characters(3, 1).Font.ColorIndex = 5

End Select



End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Absolutely great, thank you. I removed the second line because because I need it to work in entire sheet. But what if I need it in certain range of cells like A1:G20?
 
C

Claus Busch

Hi,

Am Tue, 12 Mar 2013 13:55:39 -0700 (PDT) schrieb
(e-mail address removed):
Absolutely great, thank you. I removed the second line because because I need it to work in entire sheet. But what if I need it in certain range of cells like A1:G20?
Click to expand...

then try:
If Intersect(Target, Range("A1:G20")) Is Nothing _
Or Target.Count > 1 Then Exit Sub


Regards
Claus Busch
 
T

thelordmorgoth

Hi,



Am Tue, 12 Mar 2013 13:55:39 -0700 (PDT) schrieb

(e-mail address removed):






then try:

If Intersect(Target, Range("A1:G20")) Is Nothing _

Or Target.Count > 1 Then Exit Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Thanks so much Clause. Couldn't be much better, really appreciated.
 
T

thelordmorgoth

Thanks so much Clause. Couldn't be much better, really appreciated.
Click to expand...

I have a problem, I made all changes and it works but when I'm trying to insert a line within the range I get "RunTime error '13': Type mismatch" and when I click on the debug it points to line --- Case "ABC" --- with a yellow arrow.
 
C

Claus Busch

Hi,
Am Tue, 12 Mar 2013 16:42:23 -0700 (PDT) schrieb
(e-mail address removed):
I have a problem, I made all changes and it works but when I'm trying to insert a line within the range I get "RunTime error '13': Type mismatch" and when I click on the debug it points to line --- Case "ABC" --- with a yellow arrow.
Click to expand...

the code belongs to the code module of your sheet. Right click on sheet
tab => Show Code. And in Worksheet_Change event you have to work with
target. Your strings will be colored when you enter them.


Regards
Claus Busch
 
E

edrikhart

Hi,

Am Tue, 12 Mar 2013 16:42:23 -0700 (PDT) schrieb

(e-mail address removed):






the code belongs to the code module of your sheet. Right click on sheet

tab => Show Code. And in Worksheet_Change event you have to work with

target. Your strings will be colored when you enter them.





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

I kinda fixed the issue so that was the reason that I removed the post. Now, I know the reason so I re-corrected it in right way not with the tricky way. Appreciated.
 
T

thelordmorgoth

Well let explain in very simple way.



When I input certain words in any cell in a specific range like "ABC" ,"CDE", "EFG", they automatically turn to same the words BUT multi-color. For example:



ABC changes to A(in Red)B(in Green)C(in Blue)



and same for the other words with different colors, I only need 5 words combinations.



I tried the conditional format but it applies the whole cell.



Thank you.
Click to expand...

I kinda fixed the issue so that was the reason that I removed the post. Now, I know the reason so I re-corrected it in right way not with the tricky way. Appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
How do I split a cell into text and number 3
Non-macro method (like conditional formatting) to insert text in cell? 7
Change text box background color depending on value in Label Reports 0
change cell color depending on two cell values 1
Change Format of Active Cell 1
What is the best way to program the custom cell format 2
Conditional formatting in Excel_Strange behaviour 6

Top