Inserting a line in a database - alternative colours

[rammieib]

Hi. I need help when inserting a line in a database table. Macro below.
However, what I would like is that each line in this database is a
different fill colour, say grey, white, grey, white so I can easily
distinguish between them. How can I write into the macro to do this so
it knows what the colour of the line it is inserting should be?

Cheers

Application.ScreenUpdating = False
Range("A9:FI9").Select
Selection.Insert Shift:=xlDown
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)

.Weight = xlThin

End With
Selection.Font.Bold = False
Range("A9").Select
Range("B10:E10").Select
Selection.Copy
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Range("A9").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

 

[Dave Peterson]

How about dropping the colors (and maybe the macro) and use format|Conditional
formatting.

Chip Pearson shows how:
http://www.cpearson.com/excel/banding.htm

 

[rammieib]

Cannot do - new lines in the database, starting from the top to show
the most recent need to entered often.

 

[Dave Peterson]

So why doesn't format|conditional formatting work?

 

[rammieib]

Because what do I conditional Format it to? I have subject headings
which include porject name, project buyer etc so a row of these details
gets included? When I insert a new row into the table (by moving all
the others down) I would like that row to be either white or grey,
depending on what the row below it is.

The only way I can think of to conditional format it is to put an extra
column in with a number for each entry in the database and then colour
the cells in the row depending on whether it is odd or even but I
wouldn't know how to write that in the 'formula is' bit in the
coditional formatting and don't really want an extra column.

Ideally all I need is a few lines of code which for each cell in the
row I can look at the colour of the cell below and if it is white, make
it grey and if it is grey, make it white.

 

[rammieib]

Because what do I conditional Format it to? I have subject headings
which include porject name, project buyer etc so a row of these details
gets included? When I insert a new row into the table (by moving all
the others down) I would like that row to be either white or grey,
depending on what the row below it is.

The only way I can think of to conditional format it is to put an extra
column in with a number for each entry in the database and then colour
the cells in the row depending on whether it is odd or even but I
wouldn't know how to write that in the 'formula is' bit in the
coditional formatting and don't really want an extra column.

Ideally all I need is a few lines of code which for each cell in the
row I can look at the colour of the cell below and if it is white, make
it grey and if it is grey, make it white.

 

[Peter T]

Select your table (perhaps one row below your headings) and apply this CF

Formula Is
=AND(MOD(ROW(),2),$A$1 = 0)

This is an oft' quoted method though includes the additional option to
toggle off/on
by entering/deleting some value in A1

Enter new rows 'within' the table, ie above the bottom line you applied the
CF's

Regards,
Peter T

Because what do I conditional Format it to? I have subject headings
which include porject name, project buyer etc so a row of these details
gets included? When I insert a new row into the table (by moving all
the others down) I would like that row to be either white or grey,
depending on what the row below it is.

The only way I can think of to conditional format it is to put an extra
column in with a number for each entry in the database and then colour
the cells in the row depending on whether it is odd or even but I
wouldn't know how to write that in the 'formula is' bit in the
coditional formatting and don't really want an extra column.

Ideally all I need is a few lines of code which for each cell in the
row I can look at the colour of the cell below and if it is white, make
it grey and if it is grey, make it white.

 

[Dave Peterson]

Look at that link to Chip's site.

Because what do I conditional Format it to? I have subject headings
which include porject name, project buyer etc so a row of these details
gets included? When I insert a new row into the table (by moving all
the others down) I would like that row to be either white or grey,
depending on what the row below it is.

The only way I can think of to conditional format it is to put an extra
column in with a number for each entry in the database and then colour
the cells in the row depending on whether it is odd or even but I
wouldn't know how to write that in the 'formula is' bit in the
coditional formatting and don't really want an extra column.

Ideally all I need is a few lines of code which for each cell in the
row I can look at the colour of the cell below and if it is white, make
it grey and if it is grey, make it white.