highlighting rows in excel

  • Thread starter Thread starter Davie
  • Start date Start date
D

Davie

I've got the following type of data:

id name address postcode
1 davie london lon34
1 davo glasgow ga23
3 tester manchester ma45
3 tas sfdfs sdfs
4 adssf 4asfd sf
4 dfsdf sdfsdf sdfh44


How can I highlight the rows with
1, 1,
3, 3,
and 4,4 with alternating background colours?

for example the first match would be blue(1,1)
the second match would be yellow(2,2)
the third match would switch back to blue (3,3)

and so on. The reason being, I need to visually examine the rows with
matching ids to determine whether they refer to the same entry. I know i
could probably do this in VB only I'm not too familiar with excel and vb.
 
Hi Davie

Without using colours, highlight the header row, then
Data>Filter>Autofilter.
Use the dropdown on ID to select 1, and the list would be filtered to
show just all the 1's etc.

Alternatively, use another column and number it from 1 onward to know
the existing sort order.
Mark the complete range of data, then Data>Sort> use ID > Ascending.
All the identical ID's will be together to scan the results and alter
accordingly.
When finished, Sort again by the new Numbered column to revert data back
to original sort order (if required).
 
Thanks Roger,

I am currently using this kind of system. The trouble is I have over 1000
records to search through and I basically want to have the rows all on the
page, but striped to make it easier to read.

I have sorted on the rows to get the following format, but I want to
automatically highlight alternating rows in a different color. The reason
being it makes it easier to read. Obviously I don't want to use fill colour
1000 times. I guess the other way I could do it, is filter by odd number
and then highlight all the odd numbers. Do you know how to do that ?

1
1
2
2
3
3
4
4
 
Are they always grouped in pairs?

Select all the cells on the worksheet.

With the activecell in A1 (and headers only in row 1):

Format|conditional formatting
formula is:
=mod(row(),4)<2

If that gets groups of 2 rows, but not the correct two, you can adjust that
formula:

=mod(row()-###,4)<2

Do a little experimentation with ### to get your groups correct.
 
Back
Top