borders based on cell value

williamu

New Member
Joined
Mar 19, 2019
Messages
16
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>
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,332
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
How about
Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,440
Office Version
  1. 2013
Platform
  1. Windows
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.
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,332
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,199
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top