Changing Border formatting on another worksheet based off of values on active worksheet

[LukeFrost]

Hello all. First post so excuse the ignorance of formatting. I've searched what I feel would be relevant and haven't found anything conclusive.

I'm looking to have a non active worksheet change border formatting based off of cells on the active sheet.
This is what I have so far. I don't receive any errors but there is not change to the worksheet.

This code is in the object of sheet "Lineup"

If Not Intersect(Target, Range("R14:GO53")) Is Nothing Then
        If Range("P4").Value > 9 Then
            With Worksheets("Presentation").Range("P13:AG14")
                .Borders.LineStyle = xlContinuous
                .BorderAround Weight:=xlMedium
            End With
        ElseIf Range("P4").Value = 9 Then
            With Worksheets("Presentation").Range("P13:AE14")
                .Borders.LineStyle = xlContinuous
                .BorderAround Weight:=xlMedium
            End With
            With Worksheets("Presentation").Range("AF13:AG14")
                .Borders.LineStyle = xlNone
                .BorderAround Weight:=xlNone
        ElseIf Range("P4").Value = 8 Then
            With Worksheets("Presentation").Range("P13:AC14")
                .Borders.LineStyle = xlContinuous
                .BorderAround Weight:=xlMedium
            End With
            With Worksheets("Presentation").Range("AD13:AG14")
                .Borders.LineStyle = xlNone
                .BorderAround Weight:=xlNone
            End With
        End If
End If

 

[LukeFrost]

So Have worked out most of it with trial and error. It still leaves a large black line above the entries but this at least will change the formatting on the non active sheet.
If anyone knows a quicker way to do this feel free to teach me =D.

This issue for the most part is solved. although I get an error if I keep the .BorderAround Weight in. Not sure why.
So other than that getting the thick top border is the only issue I have left.

If Not Intersect(Target, Range("R14:GO53")) Is Nothing Then
        If Range("P4").Value > 9 Then
            Worksheets("Presentation").Unprotect
            With Worksheets("Presentation").Range("P13:AP14")
                .Borders.LineStyle = xlContinuous
                .BorderAround Weight:=xlMedium
            End With
        ElseIf Range("P4").Value = 9 Then
            With Worksheets("Presentation").Range("P13:AM14")
                .Borders.LineStyle = xlContinuous
                .BorderAround Weight:=xlMedium
            End With
            With Worksheets("Presentation").Range("AN13:AP14")
                .Borders.LineStyle = xlNone
'                .BorderAround Weight:=xlNone
            End With
        ElseIf Range("P4").Value = 8 Then
            With Worksheets("Presentation").Range("P13:AJ14")
                .Borders.LineStyle = xlContinuous
                .BorderAround Weight:=xlMedium
            End With
            With Worksheets("Presentation").Range("AK13:AP14")
                .Borders.LineStyle = xlNone
'                .BorderAround Weight:=xlNone
            End With
        ElseIf Range("P4").Value = 7 Then
            With Worksheets("Presentation").Range("P13:AG14")
                .Borders.LineStyle = xlContinuous
                .BorderAround Weight:=xlMedium
            End With
            With Worksheets("Presentation").Range("AH13:AP14")
                .Borders.LineStyle = xlNone
'                .BorderAround Weight:=xlNone
            End With
        ElseIf Range("P4").Value = 6 Then
            With Worksheets("Presentation").Range("P13:AD14")
                .Borders.LineStyle = xlContinuous
                .BorderAround Weight:=xlMedium
            End With
            With Worksheets("Presentation").Range("AE13:AP14")
                .Borders.LineStyle = xlNone
'                .BorderAround Weight:=xlNone
            End With
        ElseIf Range("P4").Value = 5 Then
            With Worksheets("Presentation").Range("P13:AA14")
                .Borders.LineStyle = xlContinuous
                .BorderAround Weight:=xlMedium
            End With
            With Worksheets("Presentation").Range("AB13:AP14")
                .Borders.LineStyle = xlNone
'                .BorderAround Weight:=xlNone
            End With
        ElseIf Range("P4").Value = 4 Then
            With Worksheets("Presentation").Range("P13:X14")
                .Borders.LineStyle = xlContinuous
                .BorderAround Weight:=xlMedium
            End With
            With Worksheets("Presentation").Range("Y13:AP14")
                .Borders.LineStyle = xlNone
'                .BorderAround Weight:=xlNone
            End With
        ElseIf Range("P4").Value = 3 Then
            With Worksheets("Presentation").Range("P13:U14")
                .Borders.LineStyle = xlContinuous
                .BorderAround Weight:=xlMedium
            End With
            With Worksheets("Presentation").Range("V13:AP14")
                .Borders.LineStyle = xlNone
'                .BorderAround Weight:=xlNone
            End With
        ElseIf Range("P4").Value = 2 Then
            With Worksheets("Presentation").Range("P13:R14")
                .Borders.LineStyle = xlContinuous
                .BorderAround Weight:=xlMedium
            End With
            With Worksheets("Presentation").Range("S13:AP14")
                .Borders.LineStyle = xlNone
'                .BorderAround Weight:=xlNone
            End With
        ElseIf Range("P4").Value = 1 Then
            With Worksheets("Presentation").Range("P13:AP14")
                .Borders.LineStyle = xlNone
'                .BorderAround Weight:=xlNone
            End With
            Worksheets("Presentation").Protect
        End If
End If

 

[DanteAmor]

Hi @LukeFrost.
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

If anyone knows a quicker way to do this feel free to teach me =D.

The code can be simplified like this.
According to your code, 3 cells are incremented for each number.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("R14:GO53")) Is Nothing Then
    Dim n As Long
    With Worksheets("Presentation")
      .Unprotect
      .Range("P13:AP14").Borders.LineStyle = xlNone
      n = Range("P4").Value - 1
      If n > 0 Then
        If n >= 9 Then n = 9
        With .Range("P13").Resize(2, n * 3)
          .Borders.LineStyle = xlContinuous
          .BorderAround Weight:=xlMedium
        End With
      End If
      .Protect
    End With
  End If
End Sub

----- --

So other than that getting the thick top border is the only issue I have left.

I'm not quite sure what you mean by that part, but try adding the highlighted line:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("R14:GO53")) Is Nothing Then
    Dim n As Long
    With Worksheets("Presentation")
      .Unprotect
      .Range("P13:AP14").Borders.LineStyle = xlNone
      n = Range("P4").Value - 1
      If n > 0 Then
        If n >= 9 Then n = 9
        With .Range("P13").Resize(2, n * 3)
          .Borders.LineStyle = xlContinuous
          .BorderAround Weight:=xlMedium
          .Borders(xlEdgeTop).Weight = xlThick
        End With
      End If
      .Protect
    End With
  End If
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[LukeFrost]

Thank you for your time in helping with this!

I see your view on correlating the cell value to a math function instead of writing each variable out. I am definitely going to play around with that idea to clean up the code!
Appreciate the perspective my friend!

The thick black line must have been a graphic bug as I cleared the cells and ran the code again and it went away