Conditional formatting “containsâ€

  • Thread starter Thread starter Amy
  • Start date Start date
A

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!
 
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
 
With your data in Col A

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

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

If this post helps click Yes
 
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?
 
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.
 
Back
Top