Macro to Format Cell Borders

Z

zaska

Well-known Member
Joined
Oct 24, 2010
Messages
1,046
Hi,

I want to format bottom doubleborder for all the cells having = sum() formula . How can i achieve this with End structure

Thank you
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
I tried the formula but don't have any idea about how to find if a cell has =SUM() Formula

Code: 
Sub Macro1()
With Application
    [COLOR=Red].Find ???????? = sum()[/COLOR]
    .Borders (xlEdgeBottom)
    .LineStyle = xlDouble
      
End Sub
 
Upvote 0
zaska said:
I want to format bottom doubleborder for all the cells having = sum() formula.
Click to expand...
This is probably how I would do it...

Code: 
Sub DoubleUnderlineSumFormulas()
  Dim Cell As Range
  Application.ScreenUpdating = False
  Cells.Replace "=SUM(", "=XXXSUM(", xlPart
  For Each Cell In Cells.SpecialCells(xlFormulas, xlErrors)
    Cell.Borders(xlEdgeBottom).LineStyle = xlDouble
  Next
  Cells.Replace "=XXXSUM(", "=SUM(", xlPart
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thanks Rick

Could you further add to achieve the following format for every selected range.

<table border="0" cellpadding="0" cellspacing="0" width="358"><col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="mso-width-source:userset;mso-width-alt:3254;width:67pt" width="89"> <col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;width:72pt;font-size: 11.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:none;border-bottom:.5pt solid windowtext;border-left:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="96">Particulars</td> <td class="xl69" style="width:67pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid windowtext;background:#4F81BD; mso-pattern:#4F81BD none" width="89">Amount</td> <td class="xl68" style="width:71pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid windowtext;border-right:none; border-bottom:.5pt solid windowtext;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="94">Particulars2</td> <td class="xl70" style="width:59pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid windowtext;background:#4F81BD; mso-pattern:#4F81BD none" width="79">Amount3</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">Opening stock</td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> 10,000.00 </td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">Sales</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> 40,000.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext" height="20">Purchases</td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext"> 50,000.00 </td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">Closing Stock</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext"> 15,000.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">Expenses</td> <td class="xl64" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> 20,000.00 </td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">
</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> </td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl63" style="height:15.75pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid windowtext;border-left:.5pt solid windowtext" height="21"> </td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid windowtext;border-right:.5pt solid windowtext; border-bottom:2.0pt double windowtext;border-left:.5pt solid windowtext"> 80,000.00 </td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid windowtext;border-left:none"> </td> <td class="xl67" style="font-size:11.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid windowtext;border-right:.5pt solid windowtext; border-bottom:2.0pt double windowtext;border-left:.5pt solid windowtext"> 55,000.00 </td> </tr> </tbody></table>
Header : Bold (Particulars and Amount ) , Number Format : 0.00 and Borders
 
Upvote 0
I made the following change but i am unaware of the rest

Code: 
Sub DoubleUnderlineSumFormulas()
  Dim Cell As Range
  Application.ScreenUpdating = False
  Cells.Replace "=SUM(", "=XXXSUM(", xlPart
  For Each Cell In Cells.SpecialCells(xlFormulas, xlErrors)
[COLOR=Red]     Cell.Borders(xlEdgeTop).LineStyle = xlContinuous[/COLOR]
    Cell.Borders(xlEdgeBottom).LineStyle = xlDouble
  [COLOR=Red]  Cell.Font.Bold = True[/COLOR]
  Next
  Cells.Replace "=XXXSUM(", "=SUM(", xlPart
  Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
Try this...

Code: 
Sub DoubleUnderlineSumFormulas()
  Dim Cell As Range
  Application.ScreenUpdating = False
  Cells.Replace "=SUM(", "=XXXSUM(", xlPart
  For Each Cell In Cells.SpecialCells(xlFormulas, xlErrors)
    Cell.Borders(xlEdgeTop).LineStyle = xlContinuous
    Cell.Borders(xlEdgeBottom).LineStyle = xlDouble
    Cell.Font.Bold = True
    Cell.NumberFormat = "0.00"
  Next
  Cells.Replace "=XXXSUM(", "=SUM(", xlPart
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
How to format the other cells in the range which doesn't contain = sumformula.

I wish to format the Heading in Bold and Centred and the remaining nubers in 0.00 format (comma seperated).

I wish to apply normal border to the remaing cells.

I also noticed that number format didn't contain a comma separator

Sample

<table border="0" cellpadding="0" cellspacing="0" width="350"><colgroup><col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:3547;width:73pt" width="97"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;width:74pt;font-size: 11.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" align="center" height="20" width="99">Particulars </td> <td class="xl67" style="border-left:none;width:56pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" align="center" width="75">Amount </td> <td class="xl67" style="border-left:none;width:73pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" align="center" width="97">Particulars</td> <td class="xl67" style="border-left:none;width:59pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" align="center" width="79">Amount</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">Opening stock </td> <td class="xl70" style="border-left:none;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> 10,000.00 </td> <td class="xl68" style="border-left:none;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none">Sales </td> <td class="xl70" style="border-left:none;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> 40,000.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext" height="20">Purchases </td> <td class="xl70" style="border-left:none;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext"> 50,000.00 </td> <td class="xl68" style="border-left:none;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext">Closing Stock </td> <td class="xl70" style="border-left:none;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext"> 15,000.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">Expenses </td> <td class="xl70" style="border-left:none;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> 20,000.00 </td> <td class="xl68" style="border-left:none;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none">
</td> <td class="xl70" style="border-left:none;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl66" style="height:15.75pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext;border-left:.5pt solid windowtext" height="21">
</td> <td class="xl69" style="border-left:none;font-size:11.0pt;color:black; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid windowtext;border-right:.5pt solid windowtext; border-bottom:2.0pt double windowtext;border-left:.5pt solid windowtext"> 80,000.00 </td> <td class="xl66" style="border-left:none;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext;border-left:.5pt solid windowtext">
</td> <td class="xl69" style="border-left:none;font-size:11.0pt;color:black; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid windowtext;border-right:.5pt solid windowtext; border-bottom:2.0pt double windowtext;border-left:.5pt solid windowtext"> 55,000.00 </td> </tr> </tbody></table>
Thanks
 
Last edited:
Upvote 0
I tried this

Code: 
Sub DoubleUnderlineSumFormulas()
  Dim Cell As Range
  Application.ScreenUpdating = False
  Cells.Replace "=SUM(", "=XXXSUM(", xlPart
  For Each Cell In Cells.SpecialCells(xlFormulas, xlErrors)
    Cell.Borders(xlEdgeTop).LineStyle = xlContinuous
    Cell.Borders(xlEdgeBottom).LineStyle = xlDouble
    Cell.Font.Bold = True
   [COLOR=Red] Cell.NumberFormat = "#,##0.00"[/COLOR]
  Next
  Cells.Replace "=XXXSUM(", "=SUM(", xlPart
  Application.ScreenUpdating = True
End Sub
I am unable to proceed to find the cells which doesn't contain =sumformula and in identifying the Heading Part and format the same
 
Upvote 0
Code: 
Sub DoubleUnderlineSumFormulas()
  Dim Cell As Range
  Application.ScreenUpdating = False
  Cells.Replace "=SUM(", "=XXXSUM(", xlPart
  For Each Cell In Cells.SpecialCells(xlFormulas, xlErrors)
    Cell.Borders(xlEdgeTop).LineStyle = xlContinuous
    Cell.Borders(xlEdgeBottom).LineStyle = xlDouble
    Cell.Font.Bold = True
    Cell.NumberFormat = "#,##0.00"
  Next
  Cells.Replace "=XXXSUM(", "=SUM(", xlPart
  Application.ScreenUpdating = True
End Sub

The above code is also formatting cells which have formulas in the form (=A+B)

How to avoid this and make it work only for cells having (=Sum() ) formulas

Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Sheet code for conditional formatting
2 3
Replies
22
Views
502
MeaclH
M
C
  • Question Question
Adding New Data to Table with Inconsistent Entries Using VBA
Replies
0
Views
198
Curious George_1
C
T
  • Question Question
Indicating a zero total in a range of cells by colouring the cells either by formula or Conditional Formatting
Replies
1
Views
146
Peter_SSs
Peter_SSs
U
  • Solved
VBA code with Formula that applies a Conditional Formatting border to a range of cells
Replies
9
Views
1K
Peter_SSs
Peter_SSs
R
  • Question Question
Is there a way using a Formula to remove all formatting such as borders etc if a test is false?
Replies
2
Views
170
RudeBoy
R

Forum statistics

Threads
1,224,594
Messages
6,179,794
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top