VBA to conditional format borders

[Olivv]

Hi,

I've been looking for a VBA code to format borders with conditions.
I searched on the internet but I only found results for conditional formatting.

I know I can use conditional formatting, but I don't want to because conditional formatting tends to mess up when we move or delete rows, so I'm only looking for a VBA code.

Here is what I am looking for:
Each time the content of the cell in column B changes, the bottom of the entire line should be like that (I think):
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDash
.Color = -16776961
.TintAndShade = 0
.Weight = xlMedium

And then, when the content of the cell in column A changes, the bottom of the entire line should be line that:
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Color = -16776961
.TintAndShade = 0
.Weight = xlMedium


Do you know how to that?

Thanks!

 

[Fluff]

Hi & welcome to MrExcel
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 2 Then
      With Range("A" & Target.row).Resize(, 5).Borders(xlEdgeBottom)
         .LineStyle = xlDash
         .Color = -16776961
         .TintAndShade = 0
         .Weight = xlMedium
      End With
   ElseIf Target.Column = 1 Then
      With Range("A" & Target.row).Resize(, 5).Borders(xlEdgeBottom)
         .LineStyle = xlContinuous
         .Color = -16776961
         .TintAndShade = 0
         .Weight = xlMedium
      End With
   End If
End Sub

This needs to go in the sheet module
Right click the sheet tab > view code > paste the above into the open code window.

 

[Olivv]

Thanks for your answer.

Unfortunately I cannot run it.

When I hit the Run button, Excel does not find any macros...

 

[Fluff]

Just change any cell in either cols A or B

 

[Olivv]

Oh that is not really what I intended to do.

The problem is that code requires me to insert data in each cell. But I am not inputting any data for the file I want to use that VBA code on. I plan to use it on a file that already has data in it, with a thousand rows.

Also, I need to get dotted lines each time the cell content of a cell in column B is different from the content of the cell below, and then I need continuous lines each time the content in a cell of column A is different from the cell below.

 

[Olivv]

I just read my first post again. I understand now why you gave me that code.
Sorry I wasn't explicit enough in my first post!

 

[Fluff]

Ok try this

Sub RuledLines()

   Dim Cl As Range
   
   For Each Cl In Range("A4", Range("A" & Rows.Count).End(xlUp))
      If Cl.Value <> Cl.Offset(1).Value Then
         With Cl.Resize(, 5).Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlMedium
         End With
      ElseIf Cl.Offset(, 1).Value <> Cl.Offset(1, 1).Value Then
         With Cl.Resize(, 5).Borders(xlEdgeBottom)
            .LineStyle = xlDash
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlMedium
         End With
      End If
   Next Cl
End Sub

This needs to go in a regular module

 

[Olivv]

Awesome thanks!
You saved me so much time!

 

[Fluff]

Glad to help & thanks for the feedback