I'm doing an Excel database sheet that involves names scores and yes/no cells.

Basically I have

| Name | ID# | Department | yes/no answer | score | score | score |

about 700 rows long. Is there a way to set it so that a number less than X would make the cell a different color, and an answer of no, for example, would make that cell a different color?

Excel n00b here. I
I've tried googling some stuff, but I'm not sure what to even look for.

TheToast

United States4808 Posts

January 09 2012 18:57 GMT

You can determine text color via formula, but there is no way to dictate a cell fill color with a formula.

ComaDose

Canada10357 Posts

January 09 2012 19:01 GMT

im a newb too but what i would do if i was working around that would be to sort by the number in question
grab all the cells above the break off and highlight them a colour.
your only talking 700 cells and something like ten score columns? then you can sort by name or id number again.
if you need to preserve the order it is in now then you can add a new column to number it (even temporarily)
edit: clarity

MisterD

Germany1338 Posts

January 09 2012 19:02 GMT

You could make a column that you set to 1 or 0 (or true/false, whatever works) depending on score being >700 or <700, then sort your table by that column first and then all your failing candidates will be on top and the rest at the bottom. That would give you the advantage of being able to just look through all matching candidates without having to check the color for each one first again and again.

Sm3agol

United States2055 Posts

January 09 2012 19:06 GMT

Good ideas MisterD and ComaDose. No exactly what I was looking for, but it will work.

Melchior

United States112 Posts

January 09 2012 19:10 GMT

What version of Excel are you using? If it's 2007 or later, the Conditional Formatting tool should do exactly what you want.

Sm3agol

United States2055 Posts

January 09 2012 19:17 GMT

It's 2007. Another thing. I'm trying to sort by column, and sorting by number, but it sorts JUST that column, mixing everything up. Arghh. Pretty annoying not knowing too much about Excel. I rarely have to use it at work, but when I do it's large and I have zero help.

Edit: never mind on the bolded part, I'm just retarded.

BrTarolg

United Kingdom3574 Posts

January 09 2012 19:40 GMT

On January 10 2012 03:56 Sm3agol wrote:
I'm doing an Excel database sheet that involves names scores and yes/no cells.

Basically I have

| Name | ID# | Department | yes/no answer | score | score | score |

about 700 rows long. Is there a way to set it so that a number less than X would make the cell a different color, and an answer of no, for example, would make that cell a different color?

Excel n00b here. I
I've tried googling some stuff, but I'm not sure what to even look for.

Yes

I could write a macro for you, but youd have to pay me

jdsowa

405 Posts

January 09 2012 19:50 GMT

No need for a macro.

1. Go to the "Score" column that you want to color code and highlight all of the entries.
2. Go up to "Format" in your menu, and then pick "Conditional Formatting".
3. Make it say "Cell Value Is Less Than 700" and then click on the format button, choose the "patterns" tab and pick the color that you want the low scores to be.
4. Then click the "Add >>" button so you can add a second condition. And make that one be "More Than 699" and color it accordingly.

Edit: note that this build order is pretty all-in, if you do not do significant economic damage you will be very far behind.

TiTanIum_

Brazil1335 Posts

January 09 2012 20:04 GMT

#10

On January 10 2012 05:04 TiTanIum_ wrote:

Show nested quote +

Bidding war?

On January 10 2012 04:50 jdsowa wrote:
No need for a macro.

1. Go to the "Score" column that you want to color code and highlight all of the entries.
2. Go up to "Format" in your menu, and then pick "Conditional Formatting".
3. Make it say "Cell Value Is Less Than 700" and then click on the format button, choose the "patterns" tab and pick the color that you want the low scores to be.
4. Then click the "Add >>" button so you can add a second condition. And make that one be "More Than 699" and color it accordingly.

Edit: note that this build order is pretty all-in, if you do not do significant economic damage you will be very far behind.

I find it easier to make a UDF...

TiTanIum_

Brazil1335 Posts

January 09 2012 20:05 GMT

#11

I´m dumb. Double post.

Chill

Calgary25986 Posts

January 09 2012 20:08 GMT

#12

Ya, conditional formatting will own this.

Sm3agol

United States2055 Posts

January 09 2012 20:20 GMT

#13

On January 10 2012 05:08 Chill wrote:
Ya, conditional formatting will own this.

This man speaks the truth, and is exactly what I was looking for. Thanks.

EatThePath

United States3943 Posts

January 09 2012 22:27 GMT

#14

If it doesn't need to do it on the fly as entries are added, you could just do it super ghetto style by sorting and then coloring manually. =\

how

United States538 Posts

January 10 2012 00:45 GMT

#15

I am not sure if the 2007 version has it, but if it has the "record macro" command, it will help you out quite a lot. I am fairly sure it has it, but I am unable to check at the moment, sorry.