Turn Cell Color depending on Word

Hi all,

I just have a simple conditioning formatting question.

I want to be able to turn a cell into a certain color, depending on what I wrote in the cell. For example, any "AA" will be green and any "BB" will be red.

In fact, it'll be even better if there's another way where I can make a list (on another sheet or whatnot) that has a list of all terms that I want to turn into green (cell color) and ones that I want to turn into red (cell color). For example, there's a place where I list all the terms that I want to be highlighted in green and excel can just pull from that list and turn my my cells that has any of those terms in that list to green (on another sheet of course). If this is not possible, I guess I'd have to set the condition one by one based on the text.

Thanks!
Maven

This worked! Thank you!!

Just a question in regards to the second comment, because I'm a newbie, =COUNTIF(GreenList,A1) <-- what does A1 mean?

Thanks! So I tried applying this to my whole sheet rather than just a column (ex. A1-A3) but it started randomly highlighting empty cells green. And the cells that already are on the list to be colored green didn't get turned green. How would you apply that conditional formatting to a whole sheet rather than just a column?

I tried applying this to my whole sheet

A bit excessive in my opinion, but you apply it the same way as you would to any range of cells.

1st) Select your cell or range of cells
2nd) On the ribbon click Conditional Formatting

If you select the entire sheet, then in the Conditional Formatting window,
the Applies to Box should look like: =$1:$1048576

As a test, I tried applying Conditional Formatting to an entire sheet and it worked fine for me.

What does your word list look like?

Understand, that the cells have to match exactly, and a space is considered a character to Excel.

So Alpha and Alpha<space> as well as <space>Alpha are all seperate text strings and do not match.

Originally Posted by xenixman

I tried applying this to my whole sheet

A bit excessive in my opinion, but you apply it the same way as you would to any range of cells.

1st) Select your cell or range of cells
2nd) On the ribbon click Conditional Formatting

If you select the entire sheet, then in the Conditional Formatting window,
the Applies to Box should look like: =$1:$1048576

As a test, I tried applying Conditional Formatting to an entire sheet and it worked fine for me.

What does your word list look like?

Understand, that the cells have to match exactly, and a space is considered a character to Excel.

So Alpha and Alpha<space> as well as <space>Alpha are all seperate text strings and do not match.

So all my cells are definitely matching exactly to my original list.

When you say the "Applies to" box should look like: =$1:$1048576, where is this Applies to box? I was under the impression that I have to put =countif(Greenlist,A1) in my box in the conditional formatting section.

On the ribbon click Conditional Formatting
Select Manage Rules

In the window that opens, in the center,
there should be a small box under the words: Applies To

If you are selecting the entire sheet,
then the range of cells that the Conditional Formatting Rule is applied to
should be: =$1:$1048576

If you are selecting just a small section, say Column A & B down to row 10,
then the range of cell is: =$A$1:$B$10

Next, click on Edit Rule
Another window should open, in the bottom center,
there should be a box under the words: Format Values Where This Rule Is True
this is where your Rule Formula is entered. IE: =COUNTIF(Greenlist,A1)

So all my cells are definitely matching exactly to my original list.

Does that mean it is working?
It is highlighting only those cells that match your list?

Originally Posted by ChemistB

If you attach your workbook (Go advanced> Manage attachments) we can troubleshoot your problem

Hi all,

Thanks for the tips. However it's still not completely working 100% right like the way it's supposed to. I have attached my workbook but changed the abbreviations for confidentiality purposes. As you guys can see, I have identified the abbreviation "SY" in my sheet as to color green. But in my main sheet, I typed SY in column DC and nothing happened. Furthermore, as you can see, there are random blank cells that became colored green.

Thanks!
Maven

sample.xlsx

I checked your sample.xlsx and you have Conditional Formatting Rules all over your sheet that apply to all kind of random cells.

Best advice is to clear your whole sheet of Conditional Formats and start fresh.

To clear all your sheets of all Conditional Formatting,
On the Ribbon, Select Home Tab,
Select Conditional Formatting,
Select Clear Rules
Select Clear Rules from Entire Sheet

Yup, I did that. But take a look at this most current sheet. It's still highlighting random items (blank cells) as well as not highlighting my keyword "SY" in the column DC. I've doublechecked my conditional formatting and it was set up correctly, so was my naming convention. But how come it's still not working correctly 100%? You can check it out here:SAMPLE2.xlsx

Thanks!
Maven

I checked Sample2 and like Sample 1 it has CF rules scattered about the whole sheet.

To see what your have,
On the Ribbon, Select Home Tab,
Select Conditional Formatting,
Click on Manage Rules

At the top of the window is a box marked: Show Formatting Rules for:
Scroll this box until you get to: This Worksheet

I show Four (4) rules, all green, applied to Four (4) different ranges of cells.

Also,

If you scroll the box down to Screen 2, there is also a rule there.

Get rid of all these rules.

Select Conditional Formatting,
Select Clear Rules
Select Clear Rules from Entire Sheet

Now recheck Manage Rules and make sure they are gone.

Also most if not all of your cells have Hard Coded Colors in them,
so applying CF appears to do nothing.

You will need to clear all of the Hard Coded colors if you want to
see the CF.