Solved Conditional Formatting Excel in Win 8

February 26, 2014 at 11:27:52
Specs: Windows 8
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


See More: Conditional Formatting Excel in Win 8

Report •

#1
February 26, 2014 at 13:36:38

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


Report •

#2
February 26, 2014 at 15:09:11
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...

Report •

#3
February 26, 2014 at 16:57:01
✔ Best Answer
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 - Green

1 - 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 spreadsheet

With 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 OK twice

18 - 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 spreadsheet

A1: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 one of 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...Formats

You should be good to go.

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


Report •

Related Solutions

#4
February 26, 2014 at 18:30:58
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?

Report •

#5
February 26, 2014 at 20:12:39
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


Report •

#6
February 26, 2014 at 21:42:56
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?

Report •

#7
February 27, 2014 at 06:07:13
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 F4 right after the $E$6 shows up in the formula field. F4 will toggle through the Absolute Reference choices. Stop pressing F4 once both $'s are eliminated.

F4 can 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.


Report •

#8
February 27, 2014 at 10:32:26
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!!!!

Report •

#9
February 27, 2014 at 11:29:19
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.


Report •

#10
February 27, 2014 at 19:18:52
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.


Report •

Ask Question