I am trying to change the color of my cell if the data entered in in a specific range. I have four ranges in each cell I need color for. I have searched and read several articles about similar issues, but for some reason my computer screens do not have the same options or words that I am seeing in the articles. Please help if you can.

message edited by mmilly

The mention of "Win 8" is probably not relevant to your question. What would be relevant is the version of Excel that you are running. Excel 2003 looks very different from 2007/2010/2013. The last three look a little different from each other, but not so much that they "do not have the same options or words" for the various features. 2007 is the version in which MS Office first introduced the "ribbon" style for the menus. Those ribbons, and the features available, will look very different than the same feature in 2003.

For example, if you are reading articles related to Conditional Formatting written for Excel 2003, the "options or words" will definitely be different than the "options or words" for Conditional Formatting in an article written for Excel 2007 or later.

That said, what we would need to know is:

1 - What version of Excel are you running?

2 - What exactly are you trying to do?

3 - What "options and words" are you seeing in the articles that you do not see on your "computer screen"?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Thanks... I am using Excel 2013 in Win 8. I would like to make the "cell" turn a specific color if it is above a certain number, a different color if it is between certain numbers, and yet another color if it is below a certain number. I need to compare my data to certain ranges. I would like my data to be shown in different colors based on where my data falls in the ranges. I have tried using the "Conditional Formatting"-"Highlighted Cell Rules"- such as greater than, less than, and between. However, I am unable to see the formula or copy the formula and "paste special" into another cell to make other cells have the same formula. I can do each greater than, less than, and between separately. I will be honest I have not used Excel in a few versions. So I am lost...

There are multiple ways to use the Conditional Formatting feature in Excel 2013. For the most part I still use the old school method of "formulas". Someone else might come along with a more modern way to accomplish your goal. If I was trying to do what you are asking for, I'd do it like this:

Let's assume you want to Conditionally Format a contiguous range of cells, e.g. A1:A10. (I'll explain how to CF a non-contiguous range later)

Let's further assume these values for your 3 criteria:

Less than 10 - Blue

10 through 20 - Red

Greater than 20 - Green1 - Select A1:A10

2 - On the Home ribbon, click the Conditional Formatting

3 - Click New Rule

4 - Click "Use a formula to determine which cells to format"

5 - In the "Format values where this formula is true" enter:=A1<10

6 - Click Format

7 - Click the Fill tab

8 - Click the Blue color

9 - "OK" your way out all the way back to the spreadsheetWith A1:A10 still selected, click Conditional Formatting

10 - Click Manage rules - you should see the rule you created earlier

11 - Click New Rule

12 - Click "Use a formula to determine which cells to format"

13 - In the "Format values where this formula is true" enter:=AND(A1>=10,A1<=20)

14 - Click Format

15 - Click the Fill tab

16 - Click the Red color

17 - Click OKtwice18 - Click New Rule

19 - Click "Use a formula to determine which cells to format"

20 - In the "Format values where this formula is true" enter:=A1>20

21 - Click Format

22 - Click the Green color

23 - "OK" your way out all the way back to the spreadsheetA1:A10 should now be Conditionally Formatted for those 3 criteria.

If you need to CF a non-contiguous set of cells with the same formats...

1 - Follow the instructions above for any

oneof the cells

2 - Copy the cell

3 - Ctrl-Click all of the other cells you want to CF

4 - Right-click any of those cells

5 - Choose Paste Special...FormatsYou should be good to go.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Wow! Thank you! I am able to create all my formulas for the cell. I do have one problem.... when I copy and paste special formats, it makes the new cell dependent on the copied cell. I am not sure if I am doing something wrong.?

I can enter all the formulas and make my single cell work perfectly. I am unable to make multiple cells work from the same formula independently from each other. Any suggestions?

I'm not sure why you are having that problem. I just tried a simple test. I selected A1 and used the formula =A1>8 formatted as yellow. When I enter 9 in A1, it turned yellow. OK, CF is working.

I then copied A1, selected H5 and used Paste Special...Formats. When I used CF...Manage Rules on H5, the formula read =H5>8

Entering a 5 in A1 cleared the color in A1, entering 10 in H5 turned H5 yellow. It's working exactly as I would expect it to.

You aren't using $'s in your formulas are you? =$A$1>8 would indeed make any cell whose CF was copied from A1 dependent on A1.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Yes.. when I create the formula and click on the cell used in the formula it places $ strings. I cant always see the cell name (E6) so i click on the cell to input it in the formula. It automatically does it when I click on the cell. I will remove and hand type it all in the morning. But I wonder why it automatically inputs it when you click on the cell. is that a setting I can turn off?

I tried your method and it does indeed insert the $ signs when you click on the cell as you are building the formula. The $ indicate an Absolute Reference as described here: http://www.teach-ict.com/as_a2_ict_...

Instead of manually entering the formula or manually deleting the $'s, you should be able to eliminate them by pressing

F4right after the $E$6 shows up in the formula field.F4will toggle through the Absolute Reference choices. Stop pressingF4once both $'s are eliminated.

F4can also be used in the spreadsheet's formula bar to toggle through the Absolute Refeernce choices.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you for your help! I do not like the way Excel 2013 functions. I like the old way better. I do not like it assuming I want symbols in my formula I do not put there. I have a working spreadsheet now.. I will apply what you have taught me to the other cells I need to color adjust! Thanks so much for your help!!!! Thanks... Thanks... Thanks!!!!

re: " I do not like it assuming I want symbols in my formula I do not put there."I think I have a machine at home that still has Excel 2000 on it. I'l have to see how that version deals with clicking in the cell when building the CF formula.

If I were you, I would be prepared to not be so disappointed with Excel 2013. My guess is that the "clicking" is going to set the cell reference to Absolute ($) even as far back as Excel 2000.

I'll let you know.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Just FYI...Excel 2000 does the same thing as 2013 when you click a cell to insert it into a Conditional Formatting formula. Absolute Reference $A$1

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History