Inserting a line in a database - alternative colours

  • Thread starter Thread starter rammieib
  • Start date Start date
R

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
 
Cannot do - new lines in the database, starting from the top to show
the most recent need to entered often.
 
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.
 
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.
 
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

rammieib said:
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.
Click to expand...
 
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.
Click to expand...
 
Back
Top