MrExcel Publishing
Your One Stop for Excel Tips & Solutions

conditional formatting


Posted by Kathi on October 16, 2001 6:26 AM

I have a column with 5 digit numbers. If one of those numbers contains a 76 within the five digits, I need to have the background turn yellow. I have tried conditional formatting using *76* (equal to) but that is not getting it. Anyone?


Posted by Mark W. on October 16, 2001 6:34 AM

For a value in cell A2 usa a "Formula Is"
condition with a formula of
=ISNUMBER(SEARCH("*76*",A2)).

Posted by Aladin Akyurek on October 16, 2001 6:36 AM

Select "Formula Is" for Condition 1 and use:

=ISNUMBER(SEARCH(76,A1))

in the Conditional Formatting window.

Aladin

Posted by Gyula Lorant on October 16, 2001 6:37 AM

Try =SEARCH("76",A1)>0

Posted by Kathi on October 16, 2001 6:50 AM

Sorry, this still seems to not want to work. Is your formula pre-supposing the value is in column A?

Posted by Aladin Akyurek on October 16, 2001 6:59 AM

Kathi,

I supposed a range in A from A1 on as an example. Select your range and use the formula I suggested where you replace A1 with the first cell of your range without $-signs around.

Aladin

Posted by Kathi on October 16, 2001 7:22 AM


I feel so dumb. This is still not working. The data is in b3:b55. I am going to cond formatting, where formula is,putting in "=isnumber(search(76,b3:b55))" and choosing yellow as the desired format. I hope you can help.

Posted by .. on October 16, 2001 7:28 AM

or just look in the help file

Posted by Dan on October 16, 2001 7:31 AM

Highlight the cells b3 to b55. Choose conditional formatting, formula is:
=ISNUMBER(SEARCH(76,B3))

The B3 will automatically increment to B4, B5... on down.

Posted by Aladin Akyurek on October 16, 2001 7:34 AM

Kathi,

Lets start over.

Select B3:B55.
Activate Format|Conditional Formatting.
Select "Formula Is" for Condition 1.
Enter

=ISNUMBER(SEARCH(76,B3))

Note. The 2nd arg of SEARCH is just B3, not B3:B55.
Activate Format.
Choose yellow on the Patterns tab.
Click OK.
Click OK.

Aladin

=============

Posted by Kathi on October 16, 2001 7:51 AM

Lets start over. Select B3:B55. Activate Format|Conditional Formatting. Select "Formula Is" for Condition 1. Enter =ISNUMBER(SEARCH(76,B3)) Note. The 2nd arg of SEARCH is just B3, not B3:B55. Activate Format. Choose yellow on the Patterns tab. Click OK. Click OK. =============

*laughing.so.hard*
I can hear you thinking very loudly...what is this idiot doing anywhere near a computer?!?!?! Thank you a million times, this works, and extra thanks for your patience (and the great laugh as well!).