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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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

C
  • Question Question
Search/Find specific word or match in multiple columns and highlight the cells that do not match
Replies
1
Views
37
chueeee
C
M
  • Solved
Unique() filter to exclude blanks and multiple strings
Replies
2
Views
526
metcala
M
serge
  • Question Question
#N/A cell skip.
Replies
3
Views
267
Peter_SSs
Peter_SSs
ExcelNewbie2020
  • Solved
extract the header of sets of non empty cell
Replies
3
Views
94
ExcelNewbie2020
ExcelNewbie2020
A
  • Solved
Return Records if a match is found
Replies
4
Views
167
agentkramr
A

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

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