Conditional formatting “containsâ€

[Amy]

I would like to be able to conditionally format data that is contained within
a cell even if there is other data in the cell.

For example:

A2 - East-NE-NO-ETC
A3 - East-NE-TP-Critical
A4 - East-SE-NO-Investment
A5 - East-SE-CM-Investment

I’d like to be able to conditionally format all of the cells in Column A
that contain “NE†to be one color, and all that contain “SE†to be another
color.

Is that possible? How? Thanks in advance for any input!

 

[Jacob Skaria]

1. Select the range

2. From menu Format>Conditional Formatting>

3. For Condition1>Select 'Formula Is' and enter the below formula
=FIND("-NE-",D1)
Click Format Button>Pattern and select your color (say Red)

4. Click on Add button.

5. For Condition2>Select 'Formula Is' and enter the below formula
=FIND("-SE-",D1)
Click Format Button>Pattern and select your color (say Orange)

6. Hit OK

 

[Jacob Skaria]

With your data in Col A

1st condition
=FIND("-NE-",A1)

2nd condition
=FIND("-SE-",A1)

If this post helps click Yes

 

[Amy]

Jacob,

Thanks for the response!

This works if I'm only formatting a single cell (i.e. Formula is
=FIND("-NE-",A2) will format cell A2.)

However, I can't get it to work for an entire column. Even if I select the
column and change the formula to =FIND("-NE-",A:A) it is ineffectual. I
adjusted the formula several ways with no success.

Any ideas for getting the same concept to work on a column of data?

 

[Amy]

I'm an idiot. Thanks!

With your data in Col A

1st condition
=FIND("-NE-",A1)

2nd condition
=FIND("-SE-",A1)

If this post helps click Yes

 

[David Biddulph]

Don't adjust the formula. Select your whole column, and then if A2 is the
active cell use your =FIND("-NE-",A2) as the CF formula.
When you look in A3, you will see that the formula is =FIND("-NE-",A3), and
so on.

An alternative is to format the first cell, then use either format painter
or Copy and Edit/Paste Special/ Formats to copy the format down the rest of
the column.