grouping and give colours
 

i have a worksheet and teh driver s will key in these information:

date vehicle no km_start km_end distance travelled


at the end of the month, i will do a SORT starting with the vehicle
num. So all the

same vehicle no will be grouped together . Now , i was thinking if it
is possible

to give a alternate colour at each change of the vehicle no.? Can
conditional

formatting help to solve this problem?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=474681

Indeed you can

Select all the cells from row 2 on
FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =$B2<$B1
Select format
Select Pattern tab
Choose a colour
OK

--
HTH

Bob Phillips

"cjjoo" wrote in
message ...

i have a worksheet and teh driver s will key in these information:

date vehicle no km_start km_end distance travelled


at the end of the month, i will do a SORT starting with the vehicle
num. So all the

same vehicle no will be grouped together . Now , i was thinking if it
is possible

to give a alternate colour at each change of the vehicle no.? Can
conditional

formatting help to solve this problem?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=474681

Hi Bob and cjjoo,
I think the poster (cjjoo) would want those of the same driver together to be
of the same color, rather than just a border. see
Color Grouping with alternating colors (#grouping)
http://www.mvps.org/dmcritchie/excel...t.htm#grouping

I tried to tell someone your method was almost as good at identifying
changes -- they didn't buy it.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Bob Phillips" wrote in message ...
Indeed you can

Select all the cells from row 2 on
FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =$B2<$B1
Select format
Select Pattern tab
Choose a colour
OK

--
HTH

Bob Phillips

"cjjoo" wrote in
message ...

i have a worksheet and teh driver s will key in these information:

date vehicle no km_start km_end distance travelled


at the end of the month, i will do a SORT starting with the vehicle
num. So all the

same vehicle no will be grouped together . Now , i was thinking if it
is possible

to give a alternate colour at each change of the vehicle no.? Can
conditional

formatting help to solve this problem?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=474681

I see what you mean on re-reading. Seems as good to me <g

Regards

Bob

"David McRitchie" wrote in message
...
Hi Bob and cjjoo,
I think the poster (cjjoo) would want those of the same driver together to
be
of the same color, rather than just a border. see
Color Grouping with alternating colors (#grouping)
http://www.mvps.org/dmcritchie/excel...t.htm#grouping

I tried to tell someone your method was almost as good at identifying
changes -- they didn't buy it.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Bob Phillips" wrote in message
...
Indeed you can

Select all the cells from row 2 on
FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =$B2<$B1
Select format
Select Pattern tab
Choose a colour
OK

--
HTH

Bob Phillips

"cjjoo" wrote in
message ...

i have a worksheet and teh driver s will key in these information:

date vehicle no km_start km_end distance travelled


at the end of the month, i will do a SORT starting with the vehicle
num. So all the

same vehicle no will be grouped together . Now , i was thinking if it
is possible

to give a alternate colour at each change of the vehicle no.? Can
conditional

formatting help to solve this problem?


--
cjjoo

------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread:
http://www.excelforum.com/showthread...hreadid=474681

sorry guys , the results are good but it is possible that the same
vehicle num be given one colour instead of the last vehicle no?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=474681

Did you read the first part of my web page and then the section I mentioned;
Color Grouping with alternating colors (#grouping)
http://www.mvps.org/dmcritchie/excel...t.htm#grouping

Are you entries sorted, because it is dependent upon order to color band
like vehicles. You want the example at the top of the section in yellow.
Did you create the helper column -- which should show either 0 or 1?

Column E is a helper column, not previously existing and is required as part of C.F. test
and has a formula
The Conditional Formatting formula check if the value in E is 0 or 1

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"cjjoo" wrote in message
...

sorry guys , the results are good but it is possible that the same
vehicle num be given one colour instead of the last vehicle no?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=474681

I redid the second part of the section, as it might have added to confusion
because you want the initial help column entry and formula and the
first conditional formatting formula show.

If you only care about one column, you can eliminate the test for
both columns A & B so instead of

=MOD(OFFSET($E2,-1,0)+ OR($A2<OFFSET($A2,-1,0), $B2<OFFSET($B2,-1,0)),2)

You might use
=MOD(OFFSET($E2,-1,0)+ ($A2<OFFSET($A2,-1,0)) ,2)