grouping and give colours

[cjjoo]

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 vehicl
num. So all the

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

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

formatting help to solve this problem

 

[Bob Phillips]

Indeed you can

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

 

[David McRitchie]

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/condfmt.htm#grouping

I tried to tell someone your method was almost as good at identifying
changes -- they didn't buy it.

Indeed you can

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

 

[Bob Phillips]

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

Regards

Bob

 

[cjjoo]

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?

 

[David McRitchie]

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/condfmt.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

 

[David McRitchie]

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)