Excel: change font color of a string entered

Microsoft Excel 2003 (full product)
December 24, 2010 at 07:42:15
Specs: Windows XP
Column A,B,C,D,E,F contain large text with Row 1 as headings. I want to add certain words in the row 1 but in column G,H,I,J... and want a macro to find these words in the large text and change color of those words (I won't mind one color e.g. red but choise of different colors would be cool).

A macro pasted below asks for a word and colors one (first) word in each cell.
The improvement I seek is choosing the words as entered in the Row 1 Column G,H,I,J... without need for inputing the word by calling the input box.

Earlier macro:

Sub HighLight()
Dim WS As Worksheet, c As Range
Dim FindWord As String
Dim MyStart As Long, MyLenght As Long

FindWord = InputBox(Prompt:="Phrase to Highlight", _
Title:="Enter Phrase to Highlight", Default:="")

For Each WS In Worksheets
With WS.Cells
Set c = .Find(FindWord, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MyStart = InStr(c.Value, FindWord)
MyLength = Len(FindWord)
With c.Characters(Start:=MyStart, Length:=MyLength).Font
.Size = 13
.Color = -16776961
End With
'c.Interior.ColorIndex = 35 'now green, change to your preference
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next

End Sub


See More: Excel: change font color of a string entered

Report •


#1
December 25, 2010 at 09:48:42
I'm a bit confused by your question.

You already have text in A:F right?

re: "I want to add certain words in the row 1 but in column G,H,I,J... '

Now you want to enter text in G:J, but what does the "..." mean?

Are there more columns - beyond J - that we need to be concerned with?

Is G supposed to match up with A, H with B, etc. or if the text in e.g. G is found anywhere in A:F the color of the text in that cell should change?

Perhaps an example of what you are trying to do would help.

BTW...before you post any more VBA code or data, please read the How To mentioned in the following line.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 26, 2010 at 05:03:57
Apologies for both. 1. Confusion and 2. VB Code.
No excuses but I am new to this forum.

Thanks for your interest in my post.

There is a large amount of data in column A to F, say in all rows from 1 to 100 or so. Now I want to add a word/phase in the row 1 of columns G, H, I, J and more columns (but only in row (cell) 1 in each column.) By running a macro or automatation (with the word(s) input in each of the row 1 cells in column G, H,I,J and more (upto 20 cells), each of those words should be searched in the already present text in Column A to F (any number of rows) and change color of ALL the words in the already present text (Column A to F).
Hope this is clear.
Thanks.


Report •

#3
December 26, 2010 at 10:28:09
As I said in my response:

An example of what you are trying to do would help.

You said "word/phase" and there's a big difference as to how we might search for words and/or phrases and whether we're searching partial cells or only complete cells.

For example, let's say you have this:

           A     
1  The house is blue.
2  I own a white car.

Now if you enter this in G1:

The blue car is parked next to a white house.

Would you expect that The, blue, car, white, is, a and house would all change color?

You did say "change color of ALL the words in the already present text", right?

As you can see, thre are so many different ways that your stated requirements could be interpreted that you really need to provide some examples of what you are looking for.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 26, 2010 at 20:02:08
DerbyDad03,

I have tried to put some example data (using pre) here. As the sentences are long (though I tried to cut-short them) and the preview did not really showed me all of it. There could be some mismatch (but separation by Tab or 5 spaces is there to know data from the next column). My apologies again.

Now in Coumn G, H, I, J and a few more I want add certain terms like Diesel, exhaust, emission, SCR, ammonia, urea or a phrase like "NOx reduc" and the macro can look into the text in Columns A-F and just highlight the input terms in the text present.

Please let me know if you need more clarity.

Thanks.


 #	Year	Company	Ti	Short	Details
1	2003	XYZ	Reducing agent concentration (NH3) 	A method for determining the reducing agent concentration (NH 3 ) in the exhaust-gas flow of an internal combustion engine, using a zeolitic NH3 gas sensor that supplies a base measuring value.	A method for determining the reducing agent concentration (NH3) in the exhaust-gas flow of an internal combustion engine with an NH3 gas sensor that supplies a base measuring value, comprising initially correcting the base measuring value by an offset value and a correction value that depends on the H2O concentration in the exhaust gas to obtain an intermediate value; and subsequently changing the intermediate value with the aid of the NOx concentration in the exhaust gas to provide a corrected NH3 measuring value. 
2	2010	ABC	EXHAUST GAS  CATALYST	An exhaust gas purifying catalyst that meets the requirement concerning Hg oxidation activity and SO 2 oxidation activity; i.e., an exhaust gas purifying catalyst which specifically reduces percent SO2 oxidation, while maintaining percent Hg oxidation at a high level.	"A catalyst for purifying an exhaust gas containing metallic mercury (Hg) comprising a composition containing oxides of (i) titanium (Ti), (ii) molybdenum (Mo) and/or tungsten (W), (iii) vanadium (V), and (iv) phosphorus (P), wherein the catalyst contains Ti, Mo and/or W, and V in atomic proportions of 85 to 97.5:2 to 10:0.5 to 10, and has an atomic ratio of P/(sum of V and Mo and/or W) of 0.5 to 1.5.
"
3	2007	ZXS	Reducing agent control 	A process for reducing agent control in an exhaust gas aftertreatment system of a combustion engine with an exhaust gas pipe in which in the direction of flow of the exhaust gas there is an SCR catalyzer.	A method of reducing agent control in an exhaust gas aftertreatment system of a combustion engine with an exhaust gas pipe in which in the direction of flow of the exhaust gas there is an SCR catalyzer, whereby a reducing agent generating system has an NOx and CO/H2 generating unit, an oxidation catalyzer, and a combined NOx storage/ammonia generating unit in the standard gas path of the reducing agent generating system.


Report •

#5
December 28, 2010 at 20:21:53
DerbyDad03,

Below is a simpler example:

               A                      B
1  The house is blue.       The house roof is red
2  I own a white car.       I changed the color to blue.

In column F, G, H in row1 i have following words or phrases respectively: blue, red, white car. These words are used to find in Column A and B, and the words in A and B are highlighted (Bold or different color).

Use of the words in F, G, H is dynamic so I can change the words and run the macro again and the highlight should change. Earlier highlight should be removed to see the new word highlights.


Report •


Ask Question