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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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

A
  • Question Question
I need help with Active X buttons in VBA...
Replies
7
Views
150
angel44e
A
C
  • Question Question
Search/Find specific word or match in multiple columns and highlight the cells that do not match
Replies
1
Views
51
chueeee
C
M
  • Solved
Unique() filter to exclude blanks and multiple strings
Replies
2
Views
714
metcala
M
serge
  • Question Question
#N/A cell skip.
Replies
3
Views
310
Peter_SSs
Peter_SSs
E
  • Solved
All Unique Combinations from 5 Columns of Data + Customizations - Excel 365
Replies
6
Views
240
Excel Jason
E

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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