borders based on cell value

[williamu]

VBA code to put borders base on cell value

if cell B7 > 0 green border
If cell B7 < 0 red border
outside borders only go

around B4 to B8​

I want to do this for B7,

around B4 to B8
D7,

around D4 to D8​

F7,

around F4 to F8​

H7,

around H4 to H8
J7, around J4 to J8​

L7, around L4 to L8
N7,

around B4 to B8​

P7,

around B4 to B8​

R7,

around B4 to B8​

_{^{<strike>
</strike>}}​

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub williamu()
   Dim i As Long
   
   For i = 2 To 18 Step 2
     Cells(4, i).Resize(5).BorderAround , xlMedium, , IIf(Cells(7, i) > 0, vbGreen, vbRed)
   Next i
End Sub

 

[JLGWhiz]

Pretty much the same, except for = 0 and this is event code that runs when changes are made to one of the cells on row 7.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim rng As Range
Set rng = Range("B7, D7, F7, H7, J7, L7, N7, P7, R7")
If Not Intersect(Target, rng) Is Nothing Then
    For Each c In rng
        If c < 0 Then
            c.Offset(-3).Resize(5).BorderAround xlContinuous, xlMedium, 10
        ElseIf c > 0 Then
            c.Offset(-3).Resize(5).BorderAround xlContinuous, xlMedium, 3
        Else
            If Not c.Borders.LineStyle = xlNone Then
                c.Offset(-3).Resize(5).Borders.LineStyle = xlNone
            End If
        End If
    Next
End If
End Sub

 

[williamu]

thank you Fluff

 

[williamu]

thank you JLGWhiz worked great

 

[Fluff]

Glad we could help & thanks for the feedback