How do I change the colour of a reoccuring word in an excel workbo

[cutilee]

cHi,
I am trying to change the colour of a reocuring word in my workbook. How do
I do that? I tried find and replace but that changes all the text in each
row. I just want a particular word to be highlighted.

For example, the word is New!
It appears quiet a few tme in some columns and I want to change the colour
to red so it stands out.

Can someone help me, I would really appreciate it.
Thanks
Kareem.

 

[akphidelt]

Press CTRL-A to highlight the entire page. Then go to Format->Conditional
Formatting and put

Cell Value is equal to New

And change the format

 

[akphidelt]

And if you want to do it for the whole workbook... click on the first sheet,
hold down the shift key, then click on the last sheet... and then do the
conditional format

 

[N Harkawat]

The easiest approach would be to put a filter on the column where you have
the word"NEW"using data-->Filter--> Autofilter command.
Then select the word "new" from the drop down
Now select the range which has the word new and simply change the color of
the selected range.

You may now remove the Autofilter

 

[cutilee]

This does not work. Nothing has changed is there another formula I can use?

 

[cutilee]

Thank you for your help.

I tried this one too but is doesn't work either. My spreadsheet consists of
a product list. I have updated the list with new products so at the end of
each name the have the word"New!".

I would like to highlight the word"New!" to red so it stands out and is
recognizable by my customers lwhen they look at this list.

 

[Ron Rosenfeld]

Thank you for your help.

I tried this one too but is doesn't work either. My spreadsheet consists of
a product list. I have updated the list with new products so at the end of
each name the have the word"New!".

I would like to highlight the word"New!" to red so it stands out and is
recognizable by my customers lwhen they look at this list.

Is New the ONLY word in the cell, or are there multiple words with only NEW to
be highlighted?

If NEW is NOT the only word in the cell, is the contents of the cell a simple
string, or is the string the result of a formula?
--ron

 

[Dave Peterson]

Your life will be lots easier if you used a separate column for that New
indicator.

You could format the entire column as Bold, Red if New is the only thing that
can go in the cells (or the cell is empty).

But if you want to change just the last 3 characters of a string in the cell,
you can use a macro.

It would do the same thing as
selecting the cell
selecting just the NEW characters in the formula bar
format|cells|and change the color to red.

In code it would look something like:

Option Explicit
Sub testme()
Dim FoundCell As Range
Dim FirstAddress As String
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
FirstAddress = ""
With .Cells
Set FoundCell = .Find(What:="*New", _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'do nothing, it wasn't found
MsgBox "Nothing found!"
Else
FirstAddress = FoundCell.Address
Do
'change the font color for the last 3 characters
With FoundCell.Characters _
(Start:=Len(FoundCell.Value) - 2, Length:=3).Font
.ColorIndex = 3
.Bold = True '????
End With

'look for the next one
Set FoundCell = .FindNext(after:=FoundCell)

If FirstAddress = FoundCell.Address Then
'at the first address
Exit Do
End If
Loop
End If
End With
End With

End Sub

This line:
With .Cells

looks at the entire worksheet.

You can limit it to a specific range with something like:

With .Range("A:A")
(for column A)

And by using What:="*New" and xlwhole in the .find statement, xl will look for
something that ends with NEW.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Gord Dibben]

If the string New! is embedded within strings try this macro.

Sub Bold_Red_String()
Dim rng As Range
Dim Cell As Range
Dim start_str As Integer
Set rng = ActiveSheet.UsedRange
For Each Cell In rng
start_str = InStr(Cell.Value, "New!")
If start_str Then
With Cell.Characters(start_str, 4).Font
.Bold = True
.ColorIndex = 3
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP