Highlighting or number color change

[muck]

I am creating a spread sheet for work.
I am trying to get my cells to change color of the numbers based on the numerical value. IE, negative numbers red.

I am trying to get 85-94 to be red, 95-105 to be yellow, and 106-110 to be green.

Is it possible?
thanks matt

[acknak]

Have you looked at conditional formatting? You define a cell style for each different appearance, then use Format > Conditional Formatting to define a value or formula that selects one of the styles.

This method is limited to three styles, which should cover your situation, or you can use a lookup table to get as many styles as you want.

[muck]

thanks, i will give it a shot tonight..

[muck]

ok, after looking at your suggested spot and more of the help area i came up with this:
[<88][RED]#;[>95][GREEN]#;[YELLOW]#;General

I just wish i could do the cell and not the numbers. The yellow is hard to read...

[tn@BeWo]

That's not acknak's suggestion.
You're using a number format rather than the suggested conditional cell format (which allows you to choose styles with colored background).

[muck]

oh.... hmm i will have to go back to the area he was talking about then..
When i originally looked at that area, it was not clear what i had to change...
thanks for the heads up...

[acknak]

I played with a number format also, but it seemed to stop working with more than three alternatives, which I think you need, judging from your description.

The formats are limited to just a few (not very nice, IMO) colors, whereas a style can change any aspect of the cell appearance and can use any color.

[muck]

i think my original problem with your suggestion of the conditional format section is my unfamiliarity of the formula to use to create what i am looking for.

The numbers format i played with for a bit before one way worked. From there i just kept altering it to get what i wanted. For what i need the colors do work.

I am still going to keep trying your suggestion until i get some thing out of it LOL...

[acknak]

Here's a sample that formats some cells containing random values.

The first sheet uses simple cell value conditions and is limited to the standard three conditions/styles.

The second sheet also uses conditional formatting, but with a formula as a condition. In this case, the formula finds the cell value in a table and applies the style specified specified there. The table can be as big as you want, so you can use as many styles as you want.

[jdanniel]

I want to do something similar, and I discussed this in a thread I started a few days ago.

I have six colors:

Red
Blue
Green
Orange
Yellow
White

I want a cell to turn the corresponding color if or when I type the color's name in it.

White, obviously, is moot.

And I'll want the cell color to be pale, so as to see the text in it, which will be black.

Will it be possible to do this? And will it be possible to do with the five colors I just listed?

Thank you.

Jd

[Villeroy]

Relative addressing is the clue to all spreadsheets. A1 does NOT refer to cell A1, $A$1 does.
[Tutorial] Absolute, relative and mixed references

Next thing you've got to understand that all conditions in the conditional format dialog are evaluated as formula expressions, including "quoted strings", unquoted range names and numbers, function_calls() and relative referencing.

acknaks trick overcomes the limitation of only 3 conditional formats utilizing VLOOKUP and STYLE.
Conditional formatting in cell A1: STYLE(VLOOKUP(A1;ZoneTable;2))
VLOOKUP looks up *this_cells_value* (relative A1) in named range ZoneTable (absolute $Zones.$A$2:$B$6) and returns a stylename from column #2. Function Style takes this style name and applies it to this cell.

[jdanniel]

Thanks for replying, but Whoa, that was over my head.

This isn't going to be all that simple, is it?

[Villeroy]

Believe me, it is rather simple when you are familiar with spreadsheet editing in general. This trick is a clever combination of the most basic spreadsheet fundamentals.
A spreadsheets provides a simplified programming language for non-programmers. This is how spreadsheets evolved since the release of Visicalc in 1979.
It is far beyond the concept of some "table editor".

[jdanniel]

Oh, I'm definitely going to research and work on this...but I'm not too proud to ask someone to hold my hand while doing it.

Right now, I'm really not sure where to start, though.

[Villeroy]

I posted one link to a tutorial on absolute/relative addressing already.
Put =VLOOKUP(A1;ZoneTable;2) in B1 on first or second sheet in acknak's document
Double-click the tiny square handle which copies the formula down along the column.
Notice how the relative address A1 changes as copied down.
See the names list left of the formula bar? It takes you to to the named range ZoneTable where the style name is looked up by a numeric value.
ZoneTable is just an alias for absolute address $Zones.$A$2:$B$6 as you can see in menu:Insert>Names>Define...

Finally, put =STYLE(B1) in C1.

[acknak]

Please refer to jdanniel's thread, here: http://user.services.openoffice.org/en/ ... 36&start=0