Automating VBA Border Concise Formula

AJLS · Sep 14, 2023

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

RoryA · Sep 14, 2023

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.

AJLS · Sep 14, 2023

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

RoryA · Sep 14, 2023

You'd have to code that logic yourself. There is nothing built-in for that.

AJLS · Sep 14, 2023

RoryA said:
You'd have to code that logic yourself. There is nothing built-in for that.

I had a feeling i was out of my depth. Thanks for the help.

Automating VBA Border Concise Formula

AJLS

New Member

Excel Facts

RoryA

MrExcel MVP, Moderator

AJLS

New Member

Attachments

RoryA

MrExcel MVP, Moderator

AJLS

New Member

Similar threads

Forum statistics

Share this page

Automating VBA Border Concise Formula

AJLS

New Member

Excel Facts

RoryA

MrExcel MVP, Moderator

AJLS

New Member

Attachments

RoryA

MrExcel MVP, Moderator

AJLS

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock