Edge Sequence Color

[smandula]

Could some bush up this vba? I need to color the edge of
the first cell. in a sequence of cells.

A B C D E
1 99 2 3 4 13
2 6 12 1 2 23

In the above example in row 1 -- 2&3&4 are in sequence thereby Cell B1
would have a yellow left hand edge colored.

Similarly, in row 2 -- 1&2 are in sequence thereby Cell C2
would have a yellow left hand edge colored

Sub ColorFirstEdgeSeq()
Dim x As Range
With Sheets("Sheet1")
Set x = .Range(.Range("A1"), .Range("E2"))
End With

For Each C In x
If C.Value = (C.Offset(0, 1).Value - 1) Then
Range(C, C.Offset(0, 1)).Select
With ActiveCell.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 36
End With

End If
Next

Range("A1").Select
End Sub

With Thanks
Steve

 

[Peter T]

Hi Steve,

bush up this vba?

not sure about that but have a go with this

Sub test2()
Dim bSeq As Boolean
Dim rw As Long, c As Long
Dim vArr
Dim rng As Range

Set rng = Selection

vArr = rng.Value

For rw = 1 To UBound(vArr)
For c = 1 To UBound(vArr, 2) - 1
If vArr(rw, c) + 1 = vArr(rw, c + 1) Then
If Not bSeq Then
bSeq = True
With rng(rw, c).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 6
End With
End If
Else
bSeq = False
End If
Next
bSeq = False
Next
End Sub

Maybe hard code Selection to your desired ref. Better include some error
handling or checks in case cells are not numeric.

Regards,
Peter T

 

[smandula]

Very elegant program.

Thanks a million

Steve