|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 01:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com