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

 

[Joe4]

This can be actually done pretty easily with Conditional Formatting.

Use this formula for Excel 2007 and later (written for cell A1):

=IFERROR(FIND("MSCI",A1),0) > 0

Use this formula for Excel 2003 and earlier (written for cell A1):

=IF(ISERROR(FIND("MSCI",A1)),0,1) > 0

 

[daniels012]

Conditional formatting will do what you want.
Go to Cell A3
Use this in the formula part =search("MCSI",A3)
That will highlight the cell if you want the row, just add the same conditional formatting in all the cells to the right.

Michael

 

[daniels012]

Forgot about catching the error!
Thanks Joe4

 

[T. Valko]

Forgot about catching the error!
Thanks Joe4

No need to worry about any errors.

If the formula returns an error that means it couldn't find MSCI and since it can't find MSCI the cell will not be formatted.

When using formulas to define CF rules, if the formula evaluates to TRUE or ANY number other than 0 then the format will be applied. If the formula evaluates to any other value the format will not be applied.

 

[jmac98]

thanks instead of creating a condition for each cell can i use a range instead of cloumn a1 us a1:a1600?

 

[jmac98]

This can be actually done pretty easily with Conditional Formatting.

Use this formula for Excel 2007 and later (written for cell A1):

=IFERROR(FIND("MSCI",A1),0) > 0

Use this formula for Excel 2003 and earlier (written for cell A1):

=IF(ISERROR(FIND("MSCI",A1)),0,1) > 0

will this only work if MSCI is the only word in the cell? it will be part of a phrase how do i isolate that particular bit of text? as of now it will either turn every cell in the column yellow, or if i switch it not highlight anything at all


are 500 cells each cell has an index in it S&P500 etc there are multiple MSCI indexes but with different endings MSCI followed by an industrial, midcap etc etc all the ones with MSCI i want highlighted yellow. do i have make a condition with each row that has the msci or can i get it were it will scan and highlight all of them with one piece of code/formula??

 

[T. Valko]

will this only work if MSCI is the only word in the cell? it will be part of a phrase how do i isolate that particular bit of text? as of now it will either turn every cell in the column yellow, or if i switch it not highlight anything at all


are 500 cells each cell has an index in it S&P500 etc there are multiple MSCI indexes but with different endings MSCI followed by an industrial, midcap etc etc all the ones with MSCI i want highlighted yellow. do i have make a condition with each row that has the msci or can i get it were it will scan and highlight all of them with one piece of code/formula??

Tell us the exact range of cells you want to format. Also, tell us what version of Excel you're using.

 

[jmac98]

Tell us the exact range of cells you want to format. Also, tell us what version of Excel you're using.

exact range E1:E1794

im using excel 2003

 

[T. Valko]

exact range E1:E1794

im using excel 2003

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