Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying cell border formatting, without shading.
Hi guys,
We use excel to design layouts for some software, and this requires a rather complex screen layout that gets emailed to many people. As such, when it's amended by other teams, they seem to like breaking the formatting... Is there a way that I can copy the borders of a cell, without taking the contents (text) of shading that is already in place? I'd really appreciate some help with this! My life would be SO much easier! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying cell border formatting, without shading.
Have you tried using Copy then Paste-Special (choosing "Formats")
Shading and text color will still get pasted, but the text/number values are not copied. Then select the area(s) just pasted and turn off the color/hilighting. This whole process could be "recorded" into a macro to clean-up after your other teams. Depending on how your cells are formatted, you could re-assign the "correct" format as part of the process too (since "Formats" applies to much more then just the borders) -- Regards, John "Jamie" wrote: Hi guys, We use excel to design layouts for some software, and this requires a rather complex screen layout that gets emailed to many people. As such, when it's amended by other teams, they seem to like breaking the formatting... Is there a way that I can copy the borders of a cell, without taking the contents (text) of shading that is already in place? I'd really appreciate some help with this! My life would be SO much easier! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying cell border formatting, without shading.
Hi John,
The problem is, I can't afford to overwrite any formatting of a cell. The plan was to copy the formatting of a 'sample' cell, and paste it across the data that comes from other teams. It's only the bordering that needs to be included, changing the colours of existing cells is exactly what I don't want to do. Any clues? Many thanks for your help this far. "John Keith" wrote: Have you tried using Copy then Paste-Special (choosing "Formats") Shading and text color will still get pasted, but the text/number values are not copied. Then select the area(s) just pasted and turn off the color/hilighting. This whole process could be "recorded" into a macro to clean-up after your other teams. Depending on how your cells are formatted, you could re-assign the "correct" format as part of the process too (since "Formats" applies to much more then just the borders) -- Regards, John "Jamie" wrote: Hi guys, We use excel to design layouts for some software, and this requires a rather complex screen layout that gets emailed to many people. As such, when it's amended by other teams, they seem to like breaking the formatting... Is there a way that I can copy the borders of a cell, without taking the contents (text) of shading that is already in place? I'd really appreciate some help with this! My life would be SO much easier! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying cell border formatting, without shading.
Try adding some VBA code that you can run via(alt-F8) from the worksheet.
Using the record-macro feature as you select a sample cell and apply the bordering. It will create MOST of the code like below. I renamed Macro1, Macro2 etc. to Border1... Add the necessary ActiveSheet.Range("xxxxx") values needed to define the ranges where you are applying the border-format. This will set the borders leaving data, colors, text/number formatting alone. Below is some example VBA code to put in a Visual Basic module. (if this code gets stored with the master and is emailed out, the Security settings will complain, but it should be ok for all your users to disable macros. (when you open the workbook, you will have to enable macros to allow running Set_Borders.) Option Explicit Sub Set_Borders() ' Call the Border# subroutine with the range(s) defined for the areas ' on the worksheet needing the border set Border1 (ActiveSheet.Range("A1:A10")) Border2 (ActiveSheet.Range("B1:B10")) Border1 (ActiveSheet.Range("C1:C10")) End Sub ' Record macros as you define each sample cell's style of borders ' Modify the "Selection." code to "target." (like below) ' Add "(target as range)" onto the macro# that was recorded ' Put the code for each cell into a seperate SUB ' The "Select.Range("F11")" statements should be deleted Sub Border1(target As Range) ' Set thin border around all cells in range On Error Resume Next target.Borders(xlDiagonalDown).LineStyle = xlNone target.Borders(xlDiagonalUp).LineStyle = xlNone With target.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With target.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With target.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With target.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With target.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With target.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With On Error GoTo 0 End Sub Sub Border2(target As Range) ' Set medium border around outside of range On Error Resume Next target.Borders(xlDiagonalDown).LineStyle = xlNone target.Borders(xlDiagonalUp).LineStyle = xlNone With target.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With target.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With target.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With target.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With On Error GoTo 0 End Sub -- Regards, John "Jamie" wrote: Hi John, The problem is, I can't afford to overwrite any formatting of a cell. The plan was to copy the formatting of a 'sample' cell, and paste it across the data that comes from other teams. It's only the bordering that needs to be included, changing the colours of existing cells is exactly what I don't want to do. Any clues? Many thanks for your help this far. "John Keith" wrote: Have you tried using Copy then Paste-Special (choosing "Formats") Shading and text color will still get pasted, but the text/number values are not copied. Then select the area(s) just pasted and turn off the color/hilighting. This whole process could be "recorded" into a macro to clean-up after your other teams. Depending on how your cells are formatted, you could re-assign the "correct" format as part of the process too (since "Formats" applies to much more then just the borders) -- Regards, John "Jamie" wrote: Hi guys, We use excel to design layouts for some software, and this requires a rather complex screen layout that gets emailed to many people. As such, when it's amended by other teams, they seem to like breaking the formatting... Is there a way that I can copy the borders of a cell, without taking the contents (text) of shading that is already in place? I'd really appreciate some help with this! My life would be SO much easier! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Product Suggestion: lock border and cell formatting only | Excel Discussion (Misc queries) | |||
Excel 2007: cell shading (not conditional formatting) | Excel Discussion (Misc queries) | |||
Cell shading with conditional formatting | Excel Discussion (Misc queries) | |||
how do I use conditional formatting for alternating cell shading? | Excel Worksheet Functions | |||
Border Formatting Disappearing when data entered into cell | Excel Discussion (Misc queries) |