Automating VBA Border Concise Formula

A

AJLS

New Member
Joined
Sep 14, 2023
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Hi,

This is the border code I have created to outline formatted cells, it is very long winded and i will be needing to copy, alter the cell references, and paste another 10 times in the same sheet to get the functionality I am looking for.

Is there a way of reducing/simplifying the code? I have a couple of errors in the code already that i need to locate and amend.


VBA Code: 
Range("O38:V114").Borders.LineStyle = Excel.XlLineStyle.XlLineStyleNone


If Range("O2") > 99.9 And Range("O2") < 102.4 Then

Range("O38:O113,S38:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 99.9 And Range("O2") < 102.4 Then

Range("S32:S78,V38:V113,O38:R38").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 99.9 And Range("O2") < 102.4 Then

Range("O38:R38,T38:V38,O114:Q114,S114:V114,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If





If Range("O2") > 97.4 And Range("O2") < 99.9 Then

Range("O39:O112,S35:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 97.4 And Range("O2") < 99 Then

Range("S35:S78,O39:R39,V39:V112").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 97.4 And Range("O2") < 99 Then

Range("O39:R39,T39:V39,O113:Q113,S113:V113,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 94.9 And Range("O2") < 97.4 Then

Range("O40:O111,S35:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 94.9 And Range("O2") < 97.4 Then

Range("S35:S78,O40:R40,V40:V111").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 94.9 And Range("O2") < 97.4 Then

Range("O40:R40,T40:V40,O112:Q112,S112:V112,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If



If Range("O2") > 92.4 And Range("O2") < 94.9 Then

Range("O41:O110,S35:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 92.4 And Range("O2") < 94.9 Then

Range("S35:S78,O41:R41,V41:V110").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 92.4 And Range("O2") < 94.9 Then

Range("O41:R41,T41:V41,O111:Q111,S111:V111,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 89.9 And Range("O2") < 92.4 Then

Range("O42:O109,S35:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 89.9 And Range("O2") < 92.4 Then

Range("S35:S78,O42:R42,V42:V109").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 89.9 And Range("O2") < 92.4 Then

Range("O42:R42,T42:V42,O110:Q110,S110:V110,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If



If Range("O2") > 87.4 And Range("O2") < 89.9 Then

Range("O43:O108,S35:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 87.4 And Range("O2") < 89.9 Then

Range("S35:S78,O43:R43,V43:V108").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 87.4 And Range("O2") < 89.9 Then

Range("O43:R43,T43:V43,O109:Q109,S109:V109,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If



If Range("O2") > 84.9 And Range("O2") < 87.4 Then

Range("O44:O107,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 84.9 And Range("O2") < 87.4 Then

Range("S35:S78,O44:R44,V44:V107").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 84.9 And Range("O2") < 87.4 Then

Range("O44:R44,T44:V44,O108:Q108,S108:V108,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 82.4 And Range("O2") < 84.9 Then

Range("O45:O106,S35:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 82.4 And Range("O2") < 84.9 Then

Range("S35:S78,O45:R45,V45:V106").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 82.4 And Range("O2") < 84.9 Then

Range("O45:R45,T45:V45,O107:Q107,S107:V107,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 79.9 And Range("O2") < 82.4 Then

Range("O46:O105,S35:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 79.9 And Range("O2") < 82.4 Then

Range("S35:S78,O46:R46,V46:V105").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 79.9 And Range("O2") < 82.4 Then

Range("O46:R46,T46:V46,O106:Q106,S106:V106,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 77.4 And Range("O2") < 79.9 Then

Range("O47:O104,S35:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 77.4 And Range("O2") < 79.9 Then

Range("S35:S78,O47:R47,V47:V104").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 77.4 And Range("O2") < 79.9 Then

Range("O47:R47,T47:V47,O105:Q105,S105:V105,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 74.9 And Range("O2") < 77.4 Then

Range("O48:O103,S35:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 74.9 And Range("O2") < 77.4 Then

Range("S35:S78,O48:R48,V48:V103").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 74.9 And Range("O2") < 77.4 Then

Range("O48:R48,T48:V48,O104:Q104,S104:V104,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 72.4 And Range("O2") < 74.9 Then

Range("O49:O102,S35:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 72.4 And Range("O2") < 74.9 Then

Range("S35:S78,O49:R49,V49:V102").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 72.4 And Range("O2") < 74.9 Then

Range("O49:R49,T49:V49,O103:Q103,S103:V103,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 69.9 And Range("O2") < 72.4 Then

Range("O50:O101,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 69.9 And Range("O2") < 72.4 Then

Range("S35:S78,O50:R50,V50:V101").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 69.9 And Range("O2") < 72.4 Then

Range("O50:R50,T50:V50,O102:Q102,S102:V102,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 67.4 And Range("O2") < 69.9 Then

Range("O51:O100,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 67.4 And Range("O2") < 69.9 Then

Range("S35:S78,O51:R51,V51:V100").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 67.4 And Range("O2") < 69.9 Then

Range("O51:R51,T51:V51,O101:Q101,S101:V101,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 64.9 And Range("O2") < 67.4 Then

Range("O52:O99,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 64.9 And Range("O2") < 67.4 Then

Range("S35:S78,O52:R52,V52:V99").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 64.9 And Range("O2") < 67.4 Then

Range("O52:R52,T52:V52,O100:Q100,S100:V100,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 62.4 And Range("O2") < 64.9 Then

Range("O53:O98,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 62.4 And Range("O2") < 64.9 Then

Range("S35:S78,O51:R53,V53:V98").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 62.4 And Range("O2") < 64.9 Then

Range("O53:R53,T53:V53,O99:Q99,S99:V99,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 59.9 And Range("O2") < 62.4 Then

Range("O54:O97,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 59.9 And Range("O2") < 62.4 Then

Range("S35:S78,O54:R54,V54:V97").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 59.9 And Range("O2") < 62.4 Then

Range("O54:R54,T54:V54,O98:Q98,S98:V98,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 57.4 And Range("O2") < 59.9 Then

Range("O55:O96,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 57.4 And Range("O2") < 59.9 Then

Range("S35:S78,O55:R55,V55:V96").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 57.4 And Range("O2") < 59.9 Then

Range("O55:R55,T55:V55,O97:Q97,S97:V97,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 54.9 And Range("O2") < 57.4 Then

Range("O56:O95,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 54.9 And Range("O2") < 57.4 Then

Range("S35:S78,O56:R56,V56:V95").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 54.9 And Range("O2") < 57.4 Then

Range("O56:R56,T56:V56,O96:Q96,S96:V96,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 52.4 And Range("O2") < 54.9 Then

Range("O57:O94,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 52.4 And Range("O2") < 54.9 Then

Range("S35:S78,O57:R57,V57:V94").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 52.4 And Range("O2") < 54.9 Then

Range("O57:R57,T57:V57,O95:Q95,S95:V95,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 49.9 And Range("O2") < 52.4 Then

Range("O58:O93,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 49.9 And Range("O2") < 52.4 Then

Range("S35:S78,O58:R58,V58:V93").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 49.9 And Range("O2") < 52.4 Then

Range("O58:R58,T58:V58,O94:Q94,S94:V94,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 47.4 And Range("O2") < 49.9 Then

Range("O59:O92,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 47.4 And Range("O2") < 49.9 Then

Range("S35:S78,O59:R59,V59:V92").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 47.4 And Range("O2") < 49.9 Then

Range("O59:R59,T59:V59,O93:Q93,S93:V93,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 44.9 And Range("O2") < 47.4 Then

Range("O60:O91,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 44.9 And Range("O2") < 47.4 Then

Range("S35:S78,O60:R60,V60:V91").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 44.9 And Range("O2") < 47.4 Then

Range("O60:R60,T60:V60,O92:Q92,S92:V92,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 42.4 And Range("O2") < 44.9 Then

Range("O61:O90,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 42.4 And Range("O2") < 44.9 Then

Range("S35:S78,O61:R61,V61:V90").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 42.4 And Range("O2") < 44.9 Then

Range("O61:R61,T61:V61,O91:Q91,S91:V91,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 39.9 And Range("O2") < 42.4 Then

Range("O62:O89,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 39.9 And Range("O2") < 42.4 Then

Range("S35:S78,O62:R62,V62:V89").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 39.9 And Range("O2") < 42.4 Then

Range("O62:R62,T62:V62,O90:Q90,S90:V90,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 37.4 And Range("O2") < 39.9 Then

Range("O63:O88,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 37.4 And Range("O2") < 39.9 Then

Range("S35:S78,O63:R63,V63:V88").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 37.4 And Range("O2") < 39.9 Then

Range("O63:R63,T63:V63,O89:Q89,S89:V89,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 34.9 And Range("O2") < 37.4 Then

Range("O64:O87,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 34.9 And Range("O2") < 37.4 Then

Range("S35:S78,O64:R64,V64:V87").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 34.9 And Range("O2") < 37.4 Then

Range("O64:R64,T64:V64,O88:Q88,S88:V88,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 32.4 And Range("O2") < 34.9 Then

Range("O65:O86,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 32.4 And Range("O2") < 34.9 Then

Range("S35:S78,O65:R65,V65:V86").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 32.4 And Range("O2") < 34.9 Then

Range("O65:R65,T65:V65,O87:Q87,S87:V87,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 29.9 And Range("O2") < 32.4 Then

Range("O66:O85,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 29.9 And Range("O2") < 32.4 Then

Range("S35:S78,O66:R66,V66:V85").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 29.9 And Range("O2") < 32.4 Then

Range("O66:R66,T66:V66,O86:Q86,S86:V86,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 27.4 And Range("O2") < 29.9 Then

Range("O67:O84,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 27.4 And Range("O2") < 29.9 Then

Range("S35:S78,O67:R67,V67:V84").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 27.4 And Range("O2") < 29.9 Then

Range("O67:R67,T67:V67,O85:Q85,S85:V85,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 24.9 And Range("O2") < 27.4 Then

Range("O68:O83,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 24.9 And Range("O2") < 27.4 Then

Range("S35:S78,O68:R68,V68:V83").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 24.9 And Range("O2") < 27.4 Then

Range("O68:R68,T68:V68,O84:Q84,S84:V84,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 22.4 And Range("O2") < 24.9 Then

Range("O69:O82,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 22.4 And Range("O2") < 24.9 Then

Range("S35:S78,O69:R69,V69:V82").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 22.4 And Range("O2") < 24.9 Then

Range("O69:R69,T69:V69,O83:Q83,S83:V83,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 19.9 And Range("O2") < 22.4 Then

Range("O70:O81,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 19.9 And Range("O2") < 22.4 Then

Range("S35:S78,O70:R70,V70:V81").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 19.9 And Range("O2") < 22.4 Then

Range("O70:R70,T70:V70,O82:Q82,S82:V82,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 17.4 And Range("O2") < 19.9 Then

Range("O71:O80,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 17.4 And Range("O2") < 19.9 Then

Range("S35:S78,O71:R71,V71:V80").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 17.4 And Range("O2") < 19.9 Then

Range("O71:R71,T71:V71,O81:Q81,S81:V81,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 14.9 And Range("O2") < 17.4 Then

Range("O72:O79,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 14.9 And Range("O2") < 17.4 Then

Range("S35:S78,O72:R72,V72:V79").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 14.9 And Range("O2") < 17.4 Then

Range("O72:R72,T72:V72,O80:Q80,S80:V80,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 12.4 And Range("O2") < 14.9 Then

Range("O73:O78,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 12.4 And Range("O2") < 14.9 Then

Range("S35:S78,O73:R73,V73:V78").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 12.4 And Range("O2") < 14.9 Then

Range("O73:R73,T73:V73,O79:Q79,S79:V79,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") > 9.9 And Range("O2") < 12.4 Then

Range("O74:O77,S36:S75,R75:R115,S77:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") > 9.9 And Range("O2") < 12.4 Then

Range("S35:S77,O74:R74,V74:V77").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") > 9.9 And Range("O2") < 12.4 Then

Range("O74:R74,T74:V74,O78:Q78,S78:V78,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If

If Range("O2") < 9.9 Then

Range("O75:O76,S36:S75,R75:R115,S76:S115").Borders(XlEdgeLeft).LineStyle _

= XlContinuous



End If



If Range("O2") < 9.9 Then

Range("S35:S76,O75:R75,V75:V76").Borders(XlEdgeRight).LineStyle _

= XlContinuous



End If



If Range("O2") < 9.9 Then

Range("O75:R75,T75:V75,O77:Q77,S77:V77,O76:Q76,T76:V76").Borders(XlEdgeTop).LineStyle _

= XlContinuous





End If
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
There doesn't seem to be much pattern to the borders, but you don't need to do the same test three times - you can use something like:

Code: 
   Range("O38:V114").Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone

   If Range("O2") > 99.9 And Range("O2") < 102.4 Then
   
      Range("O38:O113,S38:S75,R75:R115,S77:S115").Borders(xlEdgeLeft).LineStyle = xlContinuous
      Range("S32:S78,V38:V113,O38:R38").Borders(xlEdgeRight).LineStyle = xlContinuous
      Range("O38:R38,T38:V38,O114:Q114,S114:V114,O76:Q76,T76:V76").Borders(xlEdgeTop).LineStyle = xlContinuous
   
   End If

rather than repeating the test for O2 being within that range.
 
Upvote 0
Hi Rory,

Thanks for taking a look, this will trim it down still. The border outlines formatted cells that change depending on the value in G2, I was wondering if Excel could recognise a formatted and unformatted cell to then add a border in between. it will repeat in the next sections of the sheet from column O2 then W2 etc. so I am hoping to condense the code before amending and repeating.

Adam
 

Attachments

  • Example 1.png
    Example 1.png
    9.6 KB · Views: 19
  • Example 2.png
    Example 2.png
    13.5 KB · Views: 20
Upvote 0
You'd have to code that logic yourself. There is nothing built-in for that.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA - AUTO FIT, Can we remove make the sum formula in below code
Replies
1
Views
157
Joe4
Joe4
K
  • Question Question
VBA DOUBT- can we add heading color and remove blank columns in this code?
Replies
0
Views
162
Kishore1982
K
togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
1K
MARK858
MARK858
T
  • Question Question
Set border for page break
Replies
2
Views
856
tiredofit
T
T
  • Question Question
Set bottom border of page to print
Replies
0
Views
674
tiredofit
T

Forum statistics

Threads
1,223,885
Messages
6,175,182
Members
452,615
Latest member
bogeys2birdies

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