| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a line in a database - alternative colours
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Inserting a line in a database - alternative colours
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 wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Inserting a line in a database - alternative colours
Cannot do - new lines in the database, starting from the top to show
the most recent need to entered often. Dave Peterson wrote: 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 wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Inserting a line in a database - alternative colours
So why doesn't format|conditional formatting work?
rammieib wrote: Cannot do - new lines in the database, starting from the top to show the most recent need to entered often. Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Inserting a line in a database - alternative colours
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 wrote: So why doesn't format|conditional formatting work? rammieib wrote: Cannot do - new lines in the database, starting from the top to show the most recent need to entered often. Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Inserting a line in a database - alternative colours
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 wrote: So why doesn't format|conditional formatting work? rammieib wrote: Cannot do - new lines in the database, starting from the top to show the most recent need to entered often. Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Inserting a line in a database - alternative colours
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" wrote in message ups.com... 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 wrote: So why doesn't format|conditional formatting work? rammieib wrote: Cannot do - new lines in the database, starting from the top to show the most recent need to entered often. Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Inserting a line in a database - alternative colours
Look at that link to Chip's site.
rammieib wrote: 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 wrote: So why doesn't format|conditional formatting work? rammieib wrote: Cannot do - new lines in the database, starting from the top to show the most recent need to entered often. Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Inserting information from a database with a macro | Excel Discussion (Misc queries) | |||
| How do I reset the default colours in a line graph | Charts and Charting in Excel | |||
| Inserting foreign/special characters from a database | Excel Programming | |||
| Chart Default Line colours | Excel Discussion (Misc queries) | |||
| Inserting an Access database into a spreadsheet? | Excel Programming | |||
Linear Mode
