I need to conditional format in excel based the word "estimated" 1

[kidnos]

Hi, I would like to format my excel workbook and its sheets so that when one of the cells (specifically in column B)says "estimated" the row, cell or text itself will be colored red.
For example, in one of my sheets I have 6 columns of data and lets say row 1 in column B says Estimated AP then I would like that row, text or cell to be colored red. Any ideas anyone? Thanks

 

[firefytr]

Select the columns you wish to color. Go to Format | Conditional Formatting. Switch from Cell Value Is to Formula Is and enter this as your formula ...

=ISNUMBER(SEARCH("estimated",$B1:$B11,1))

This will not be case sensitive (otherwise use the FIND instead of SEARCH function) and will highlight the entire row of the cells/columns you selected if the condition is true.

Set your format as desired (click the format button). Here you can specify if you want the text colored or the background colored depending if the condition is met or not.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[firefytr]

Oh, and btw, change $B1:$B11 to the range desired, i.e. $B1:$B10, or $B1:$B100 or even $B1:$B10000.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[AnotherHiggins]

You've got the answer right in your subject line - Conditional Formatting.

-Highlight the columns to which you want to apply the Conditional Formatting.

-Go to Format > Conditional Formatting

-Change the first box to Formula Is

-In the second box, type in [COLOR=blue white]=Isnumber(Search("estimated",$B1))[/color]

-Press Format

-Go to the Patterns tab

-Select a pleasing color.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Dang. A little slow on the draw there....

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

Looks good though John. ;-)

I need to learn these board tags.. I'm used to vBulletin.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[AnotherHiggins]

Thanks.

I'm sure you've noticed this, but just in case you haven't, you can click on the link under the posting box for Process TGML for a list of all the tags TT recognizes.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

Yeah, I saw it. It's just another set of board tags to memorize. Too many boards .. so little time. ;-)

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[kidnos]

This seems to work great. However, for one of the sheets it is not highlighting the text red. I have pasted the formula above and for some strange reason it is not turning "estimated" red, but it does change the text color for the cell that has the text "transfer" as the first string. crazy....