Condtional Formatting

[Sam Harman]

Hi I have a list of numbers which I would like to automatically format
with a green background......does anyone know how I can do this

3.5
4
5
8
8 the first four values should be highlighted green
10
12
14
14
14
16
0.8 the next four value should be highlighted green
4
6.5
8
14
16

the next four and so on..........the top four values are identified by
the number to start with being smaller than the preceding number

1.2
3
3.5
8
12
2.5 From here top four values to be green
3
3.5
5.5
7.5
10
20
2.75 From here top four values to be green
3.33
5
6.5
2.75 From here top four values to be green
3
4.5
6
8
10
10
33
2.75 From here top four values to be green
3.25
5.5
10
10
12
12
12
14
16
2 From here top four values to be green
7
4.5
6.5
8
10
10

I hope this makes sense and any help appreciated

thanks

Sam

 

[CWatters]

Hi I have a list of numbers which I would like to automatically format
with a green background......does anyone know how I can do this

3.5
4
5
8
8 the first four values should be highlighted green

snip

the top four values are identified by

the number to start with being smaller than the preceding number

Try this..

Start by setting the format for one cell. Lets use A8. (Don't use A1 to
A4 for the moment)..

Using the conditional format menu format A8 green based on a formula.
Set the formulae to =OR(A8<A7,A7<A6,A6<A5,A5<A4).

Then using "manage rules" change the range of cells to which this
applies from A8 to say A4:A100

That seems to work when I try it.

However watch out for a potential problem with the first few cells (eg
A1 to A4). If you change the range to which the formatting is applied to
include A1:A4 the formulae wraps (to negative cell numbers?) and
becomes...(A1<A1048576,A1048576<A1048575,A1048575<A1048574,A1048574<A1048573)

 

[CWatters]

Then using "manage rules" change the range of cells to which this
applies from A8 to say A4:A100

oops that should read A5:A100

because..

 

[Sam Harman]

oops that should read A5:A100

because..

Hi Colin and thanks for taking the time to do this for me......it is
almost perfect but not quite

I have attached a spreadsheet to show where the error is...perhaps it
is me inputting the formula but any help for an idiot would be
appreciated lol

Thanks

Sam

 

[CWatters]

Hi Colin and thanks for taking the time to do this for me......it is
almost perfect but not quite

I have attached a spreadsheet to show where the error is...perhaps it
is me inputting the formula but any help for an idiot would be
appreciated lol

Thanks

Sam

I can't see the attachment but you can email it to me if you want.
Remove NO and SPAM from my email address.

I can't claim to be an Excel expert. Just like to keep my hand in.

Colin

 

[Sam Harman]

Ok thanks Colin, any help appreciated

Cheers

Sam

 

[CWatters]

Ok thanks Colin, any help appreciated

Cheers

Sam

Just realised my email address was set up wrong in my newsreader. It
should end .com not .net

Have made correction.