searching text and conditional format with multple conditions

  • Thread starter Thread starter diaare
  • Start date Start date
D

diaare

I am using Excel 2003.

I have a very large file with lots of text. I need to search column C for 9
specific strings of text (they can be anywhere in the cell) and if any one of
these strings are there I would like to format the row a different color.

I set up conditonal formatting with this formula:
=ISNUMBER(SEARCH("vit",$C1))

and it works good. But how do add the or condtion for the other 8 strings
of text?

Thanks,
Diane
 
With

H1:H9 containing your list of "search words".
And
C1:C100 containing your cells to test

Try this:
Select C1:C100, with C1 as the active cell

From the Excel Main Menu:
<format><conditional formatting>
Condition_1
Formula is: =MAX(INDEX(COUNTIF(C1,"*"&$H$1:$H$9&"*"),0))
Click the [format] button and set your colors.
Click [OK] to finish

That will highlight any cell containing any of the values listed in H1:H9.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Thanks so much to both of you.

I ended up going with Ron's so my user has the ability to change the search
terms as needed without editing the formula. I only wish I could reference a
seperate tab in the conditional formating.

Thanks again for all of your help,

Diane

Ron Coderre said:
With

H1:H9 containing your list of "search words".
And
C1:C100 containing your cells to test

Try this:
Select C1:C100, with C1 as the active cell

From the Excel Main Menu:
<format><conditional formatting>
Condition_1
Formula is: =MAX(INDEX(COUNTIF(C1,"*"&$H$1:$H$9&"*"),0))
Click the [format] button and set your colors.
Click [OK] to finish

That will highlight any cell containing any of the values listed in H1:H9.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



diaare said:
I am using Excel 2003.

I have a very large file with lots of text. I need to search column C for
9
specific strings of text (they can be anywhere in the cell) and if any one
of
these strings are there I would like to format the row a different color.

I set up conditonal formatting with this formula:
=ISNUMBER(SEARCH("vit",$C1))

and it works good. But how do add the or condtion for the other 8 strings
of text?

Thanks,
Diane
Click to expand...
Click to expand...
 
I only wish I could reference a seperate tab in the conditional
You can!
CF won't work with cell refernces to other sheets,
BUT it will work with Named Ranges that refer to those cells.

If your data is on Sheet3
and
your search list is on Sheet1, H1:H9

Then....assign a Range Name to the search list.

Here's a shortcut way:
Select H1:H9 on Sheet1
In the Name Box (just above the Col_A title):
Type rngMyList
Press [ENTER]

Now switch to Sheet3
Select the CF cells (again, assuming cell C1 is selected)
<format><conditional formatting>
Condition_1
Formula is: =MAX(INDEX(COUNTIF(C1,"*"&rngMyList&"*"),0))
Click the [format] button and set your colors.
Click [OK] to finish

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



diaare said:
Thanks so much to both of you.

I ended up going with Ron's so my user has the ability to change the
search
terms as needed without editing the formula. I only wish I could
reference a
seperate tab in the conditional formating.

Thanks again for all of your help,

Diane

Ron Coderre said:
With

H1:H9 containing your list of "search words".
And
C1:C100 containing your cells to test

Try this:
Select C1:C100, with C1 as the active cell

From the Excel Main Menu:
<format><conditional formatting>
Condition_1
Formula is: =MAX(INDEX(COUNTIF(C1,"*"&$H$1:$H$9&"*"),0))
Click the [format] button and set your colors.
Click [OK] to finish

That will highlight any cell containing any of the values listed in
H1:H9.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



diaare said:
I am using Excel 2003.

I have a very large file with lots of text. I need to search column C
for
9
specific strings of text (they can be anywhere in the cell) and if any
one
of
these strings are there I would like to format the row a different
color.

I set up conditonal formatting with this formula:
=ISNUMBER(SEARCH("vit",$C1))

and it works good. But how do add the or condtion for the other 8
strings
of text?

Thanks,
Diane
Click to expand...
Click to expand...
Click to expand...
 
Back
Top