Set borders dynamically with vba

kelly mort

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I am looking for a way to place borders around my cells in a unique way.

So my range is B2:J16 with columns E and H excluded.

So like in cell B2 and C2 we have same items "B" and "B" so we have the border around the range B2:C2 with no line in between B2 and C2. That's we show only border outline. Then in cell D2 since E2 is excluded we set border for only the D2 .


Then we continue in that order.

So we may have something like this :

Code: 
[COLOR=#333333]|Col B |Col C |Col D |Col E |Col F |Col G |Col H |Col I |Col J|
---------------------------------------------------------------------------------------
| B         B   |     G   |          |    G   |     I    |           |    A  |   A    |
---------------------------          ---------------------           ------------------
| A         A   |     E   |          |    D         D    |           |    F  |   F    |
---------------------------          ---------------------           ------------------
| I         I   |     F   |          |    A         A    |           |    C      C    |
---------------------------          ---------------------           ------------------
| H   |     C         C   |          |    B   |     H    |           |    D      D    |
---------------------------          ---------------------           ------------------
| B   |     D   |     H   |          |    E         E    |           |    F  |   A    |
---------------------------          ---------------------           ------------------
| I   |     B   |     G   |          |    G   |     D    |           |    C      C    |
---------------------------          ---------------------           ------------------
| A         A   |     E   |          |    D         D    |           |    F      F    |
---------------------------          ---------------------           ------------------
| I         I   |     G   |          |    A         A    |           |    C      C    |
---------------------------          ---------------------           ------------------
| H   |     C         C   |          |    B   |     H    |           |    D      D    |
---------------------------          ---------------------           ------------------
| I   |     D   |     H   |          |    E         E    |           |    B  |   A    |
---------------------------          ---------------------           ------------------
| B         B   |     G   |          |    G   |     I    |           |    C      C    |
---------------------------          ---------------------           ------------------
| A         A   |     E   |          |    D         D    |           |    F      F    |
---------------------------          ---------------------           ------------------
| I         I   |     G   |          |    A         A    |           |    C      C    |
---------------------------          ---------------------           ------------------
| H   |     C         C   |          |    B   |     H    |           |    D      D    |
---------------------------          ---------------------           ------------------
| B   |     D   |     H   |          |    E         E    |           |    F  |   A    |
---------------------------------------------------------------------------------------[/COLOR]
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
I'm not against VBA...but there may not be a need for it in this case.

Using this sample data in A1:F10
Code: 
Alpha      Bravo      Charlie    Delta      Echo       Foxtrot
B          E          E          B          E          A
D          E          A          A          A          E
C          E          A          F          E          B
F          A          B          F          A          C
C          B          F          E          C          B
C          B          B          F          B          D
B          A          D          C          C          E
A          F          D          A          C          E
B          F          B          B          B          F

Set A2:F10 borders to this combination:
Top
Bottom

Additionally,set A2:A10 to also have Left borders

Next
Select A2:F10, with A2 as the active cell
Home.Conditional_Formatting.New_Rule.Use a formula
Formula: =B2<>A2
Format: Add a right border
Click: OK

The end result will be something like this:
Code: 
 Alpha      Bravo      Charlie    Delta      Echo       Foxtrot
---------------------------------------------------------------
|B         |E          E         |B         |E         |A     |
---------------------------------------------------------------
|D         |E         |A          A          A         |E     |
---------------------------------------------------------------
|C         |E         |A         |F         |E         |B     |
---------------------------------------------------------------
|F         |A         |B         |F         |A         |C     |
---------------------------------------------------------------
|C         |B         |F         |E         |C         |B     |
---------------------------------------------------------------
|C         |B          B         |F         |B         |D     |
---------------------------------------------------------------
|B         |A         |D         |C          C         |E     |
---------------------------------------------------------------
|A         |F         |D         |A         |C         |E     |
---------------------------------------------------------------
|B         |F         |B          B          B         |F     |
---------------------------------------------------------------

Is that something you can work with?
 
Upvote 0
How about
Code: 
Sub kellymort()
   Dim Cl As Range
   With Range("B2:D16,F2:G16,I2:J16")
      .Borders.Weight = xlMedium
      For Each Cl In .Offset(0)
         If Cl = Cl.Offset(, 1) Then
            Cl.Borders(xlEdgeRight).LineStyle = xlNone
         End If
      Next Cl
   End With
End Sub
 
Upvote 0
Ron Coderre said:
I'm not against VBA...but there may not be a need for it in this case.

Using this sample data in A1:F10
Code: 
Alpha      Bravo      Charlie    Delta      Echo       Foxtrot
B          E          E          B          E          A
D          E          A          A          A          E
C          E          A          F          E          B
F          A          B          F          A          C
C          B          F          E          C          B
C          B          B          F          B          D
B          A          D          C          C          E
A          F          D          A          C          E
B          F          B          B          B          F

Set A2:F10 borders to this combination:
Top
Bottom

Additionally,set A2:A10 to also have Left borders

Next
Select A2:F10, with A2 as the active cell
Home.Conditional_Formatting.New_Rule.Use a formula
Formula: =B2<>A2
Format: Add a right border
Click: OK

The end result will be something like this:
Code: 
 Alpha      Bravo      Charlie    Delta      Echo       Foxtrot
---------------------------------------------------------------
|B         |E          E         |B         |E         |A     |
---------------------------------------------------------------
|D         |E         |A          A          A         |E     |
---------------------------------------------------------------
|C         |E         |A         |F         |E         |B     |
---------------------------------------------------------------
|F         |A         |B         |F         |A         |C     |
---------------------------------------------------------------
|C         |B         |F         |E         |C         |B     |
---------------------------------------------------------------
|C         |B          B         |F         |B         |D     |
---------------------------------------------------------------
|B         |A         |D         |C          C         |E     |
---------------------------------------------------------------
|A         |F         |D         |A         |C         |E     |
---------------------------------------------------------------
|B         |F         |B          B          B         |F     |
---------------------------------------------------------------

Is that something you can work with?
Click to expand...


Okay cool. Thank you.

It's cool
 
Upvote 0
Fluff said:
How about
Code: 
Sub kellymort()
   Dim Cl As Range
   With Range("B2:D16,F2:G16,I2:J16")
      .Borders.Weight = xlMedium
      For Each Cl In .Offset(0)
         If Cl = Cl.Offset(, 1) Then
            Cl.Borders(xlEdgeRight).LineStyle = xlNone
         End If
      Next Cl
   End With
End Sub
Click to expand...


Cute!

I love this
 
Upvote 0
Hello @Fluff,

I have used xlThin for the line width.

Now I want to use the xlMedium for the row outline of three row groupings.

That's row 2 to row 4

Then row 5 to row 7 in that order to row 16.

Please let me know if this is not clearly stated.
 
Upvote 0
How about
Code: 
Sub kellymort()
   Dim Cl As Range
   Dim i As Long
   With Range("B2:D16,F2:G16,I2:J16")
      .Borders.Weight = xlThin
      For Each Cl In .Offset(0)
         If Cl = Cl.Offset(, 1) Then
            Cl.Borders(xlEdgeRight).LineStyle = xlNone
         End If
      Next Cl
   End With
   For i = 2 To 16 Step 3
      Range("B" & i).Resize(3, 3).BorderAround Weight:=xlMedium
      Range("F" & i).Resize(3, 2).BorderAround Weight:=xlMedium
      Range("I" & i).Resize(3, 2).BorderAround Weight:=xlMedium
   Next i
End Sub
 
Upvote 0
Fluff said:
How about
Code: 
Sub kellymort()
   Dim Cl As Range
   Dim i As Long
   With Range("B2:D16,F2:G16,I2:J16")
      .Borders.Weight = xlThin
      For Each Cl In .Offset(0)
         If Cl = Cl.Offset(, 1) Then
            Cl.Borders(xlEdgeRight).LineStyle = xlNone
         End If
      Next Cl
   End With
   For i = 2 To 16 Step 3
      Range("B" & i).Resize(3, 3).BorderAround Weight:=xlMedium
      Range("F" & i).Resize(3, 2).BorderAround Weight:=xlMedium
      Range("I" & i).Resize(3, 2).BorderAround Weight:=xlMedium
   Next i
End Sub
Click to expand...

Yes this is it.

I am grateful
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Customized text wrapping to new row (not within cell)
Replies
8
Views
178
rlv01
R
J
  • Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
94
JMC57
J
A
  • Question
VBA if a row is all blank in a range of columns, highlight the blanks and the headers
2 3 4
Replies
31
Views
398
AnnAnn
A
F
  • Question
How to add if input is empty jump it
2
Replies
11
Views
180
Fluff
Fluff
J
  • Solved
Help with calculation between work days based on condition
Replies
7
Views
250
Fluff
Fluff

Forum statistics

Threads
1,214,426
Messages
6,119,411
Members
448,894
Latest member
spenstar

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