highlighting a cell that contains a specific word within a phrase

[jmac98]

i can't get the conditional formating to work. what im trying to do is look through each cell in a specific column and each cell that contains the word MSCI ( it will be one word in a phrase) highlight that row yellow. Does anyone know how to do this in VBA? Also i will need use this macro on numerous other spreedsheets is there a way to write it were it is available everytime excel is open and is not specific to this one spreadsheet?

Thanks for your time

 

[jmac98]

Try this...

Select the *entire* range E1:E1794 starting from cell E1.
Cell E1 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:

=SEARCH("MCSI",E1)

Click the Format button
Select the Patterns tab
Select a shade of yellow
OK out

HAHA thanks works perfectly

 

[Rick Rothstein]

exact range E1:E1794

im using excel 2003

Select cells E1:E1794 with E1 the active cell, then use this Conditional Formatting formula...

=COUNTIF(E1,"*MSCI*")

Don't forget to select your fill color.

 

[T. Valko]

HAHA thanks works perfectly

Good deal. Thanks for the feedback!

 

[jmac98]

if i after i do the formatting for this column, in a seperate column i want to put an if then statement formula that found when ever a cell in column E is filled yellow put a Y indicator in this column is that possible?

 

[T. Valko]

if i after i do the formatting for this column, in a seperate column i want to put an if then statement formula that found when ever a cell in column E is filled yellow put a Y indicator in this column is that possible?

You can use Rick's formula for that...

=IF(COUNTIF(E1,"*MSCI*"),"Y","")

Copy down as needed.

 

[Rick Rothstein]

You can use Rick's formula for that...

=IF(COUNTIF(E1,"*MSCI*"),"Y","")

Copy down as needed.

That formula is too long... my hands would become tired from typing so many characters

=LEFT("Y",COUNTIF(E1,"*MSCI*"))

 

[T. Valko]

That formula is too long... my hands would become tired from typing so many characters

=LEFT("Y",COUNTIF(E1,"*MSCI*"))

Yeah, the more you type, the greater the odds of making a mistape!