Highlighting Cells Containing Specific Text

FormatWords Update.
I forgot to to check for duplicate entries of a keyword, so I ran it on a processed food allergy list (humungous entries for wheat) If there are If theere are likely to be duplicates in a cell this will tak care of it. Also an error check if User clicks Cancel

Sub KeysWordsBold()
Dim c As Range, r As Variant, cell As Range
Dim str As String, s As String, pos As Long
Dim wdCount As Integer, j As Long

'when user presses Cancel exit from macro
On Error GoTo EndIt
Set r = Application.InputBox("Select Area", "Keyword Selection", Type:=8)

'Loop through the cells with text
For Each c In Selection
str = LCase(c)
'Loop through the Keywords
For Each cell In r
s = LCase(cell)
wdCount = 0
pos = 1
'is keyword duplicated in Selection?
wdCount = (Len(str) - Len(WorksheetFunction.Substitute(LCase(c), LCase(cell), ""))) / Len(cell)
If wdCount > 1 Then
For j = 1 To wdCount
pos = InStr(pos, str, s)
c.Characters(pos, Len(cell)).Font.Bold = True
pos = pos + 1
Next j
ElseIf wdCount = 1 Then
pos = InStr(pos, str, s)
c.Characters(pos, Len(cell)).Font.Bold = True
If s Like "*palm*" Then
c.Characters(pos, Len(cell)).Font.Color = vbRed
End If
End If
Next cell
Next c
EndIt:

End Sub

The palm thing is just between rainforrests and me

Sylvia, I forgot to mention that you can also select the range as normal, that is what the type:=8 is for.

Sylvia Moritz, You can have several lists and choose which one to check the text with.

Firstly select the range to format, then run the macro (I assignd it to a form button in the VB editor but you can use a text box) Then you are promptd for a range of keywords. If they are named you can type the name, e.g. probate, law etc. The sit back


Sub KeysWordsBold()
Dim c As Range, r As Range, cell As Range
Dim str As String, s As String, pos As Long

Set r = Application.InputBox("Select Area", "Keyword Selection", Type:=8)

'Loop through the cells with text
For Each c In Selection
str = LCase(c)
For Each cell In r
s = LCase(cell)
pos = 0
pos = InStr(1, str, s)
If pos > 0 Then
c.Characters(pos, Len(cell)).Font.Bold = True
End If
Next cell
Next c

End Sub

@Peter Atherton Thanks for the the great tip. I will test it out.

Using the macro would help if there was one set of keywords. I work in a law firm and there's never a single set of key words.

Thanks,

Sylvia

Sylvia Moritz
You will have to use a macro for this as Excel uses the Characters function if you record this while formatting a word within a cell.
Here is a recorded macro with rubbish removed that shows the principle.

Sub Macro1()
ActiveCell.FormulaR1C1 = "Hello World"

With ActiveCell.Characters(Start:=7, Length:=5).Font
.FontStyle = "Bold"
End With

End Sub

If you decide to go this way Make a list of the key words and then search foor each keyword using Instr to get the position to get Start and use the length of Keyword for the second variable.

HTH

Is there any possibility to highlight specific text in Excel worksheet without highlighting full cell and without using macros?

We need to highlight specific text only. not to cell.

Please provide your response.

I want to fill colour of a cell when match within other row.
I want to fill colour of a cell if any cell filled with colour in that row.

Dear Sir;

Is there any possibility to highlight specific text in Excel worksheet without highlighting full cell and without using macros?

We need to highlight specific text only. not to cell.

Please provide your response.

Best regards

I'm making a schedule, it's a detailed schedule containing several different jobs per person. I want to differentiate jobs with different colors, but in the same cell; how would I do that?

This really helped! Super easy to understand! Please update your directions for newer excel versions

I am a teacher and have set up a homework tracking sheet. In certain boxes I will either have the homework assignment, if its a break, or if there is a test. For the homework assignment, it can either have something like "pg 23 #2 - 10" "11.4 notes" or "11.6 worksheet" is there a way to, in one rule, let it find the key words pg, notes, or worksheet and highlight it the one color? I know I can do it in 3 different rules but I am trying to consolidate.

Thanks for the tips to the writer and those with added ones in the comments!

@Joe

- add a third rule and select "Use a formula to determine which cells to format"
- enter the following formula: =AND(FIND("violation letter",LOWER(A1)),FIND("repair",LOWER(A1)))
- change A1 if you use another column
- make sure it the first rule

basically I'm asking how do I write the "and" without it counting as text itself

Using this method, if I or instance want the text "violation letter" to turn the block blue, and if I want the word "repair" to turn the block yellow. I can make it happen using 2 separate rules. How would I write a third rule that turns the block green if it contains the words "violation letter and "repair" with each text having the possibility of being placed any where in the sentence?

As someone asked below, i need to highlight the searched word withing a cell. Right now when i do the search and find option it just highlights the cell which has those words, but i want the actual word within the cell to get highlighted. Like it works in word doc, you search for word and every time that word appears in the doc it gets highlighted and it even gives the number of found words.... many thanks in advance for ur help.

Rich,
As far as I'm aware you can't use arrays or ranges in conditional formatting, but you can work around it.
If you place your "trigger" numbers in a column you can use the following formula in the conditional formatting rule box
=OR(A1=$F$1,A1=$F$2,A1=$F$3)
The "trigger" numbers are in Col F in this example - then if you need to change your "triggers" you only need to change the cells in Col F.
You can include a couple of blanks on the bottom of the list (if you include them in the formula).
Hope this is helps

pls, I need you to help me. I am trying to format cells based on certain values. The cells contain values 1 to 90. I want to give a unique color to cells containing the ff. values: 31, 47, 55, 59, 61, 62, 79 and 87. I know I can do them one after the other but I need a way to them all at once.
Thanks for your help.

Thanks Steve

Sulabh,
You need to create 2 rules using "use formula to determine which cells to format" then use;

=B7="Matching"
=B7="Non Matching"

obviously change the cell address to the first cell in your range to be checked

Steve

I was working on same case where I had to highlight cell color based on 2 texts -"Matching" and "Not Matching". I created 2 separate rules for both cases.Now if even the cell values in "Not Matching", I am getting cell color defined for "Matching". Please help.

I've actually realised that you can do this simply with a find and replace search.

You can format your replacement text also. This has done the job for me.

Hi.... I am interested in knowing if there was a response to Sivahari's question regarding highlighting/formatting the found text within the cell and not the entire cell. I need to do this if possible also.

Thanking you for your assistance.

Cheers!

Thank You!!!

Hi Dave,

is it possible to make formula that search text only at the end of the sentence?
ex: Im gonna search Y in
003.0425.01.Y
and then im need to highlight those cell with Y
thanks in advance

rk
Yes, in macros you can reference cells in different workbooks, you just need to include the workbook and worksheet names in the cell reference. You can check the syntax using Record Macro - the reference to a cell in another workbook will be something like (if the workbooks are in the same folder):

[Book1]Sheet1!$B$3

If the workbooks are in different locations, the workbook reference will include the file path.

Great. thanks.

Dear Sir;

Is there any possibility to highlight specific text in Excel worksheet without highlighting full cell and without using macros?

We need to highlight specific text only. not to cell.

Please provide your response.

Best regards

i want to use cells of two or more different sheets of different excel files in a macro. is itposbbile? then how?

Awesome. Thanks for your great tips :)