Reputation: 1484
I have a question about selecting down triangle.
I know I can select the diagonal by this code:
Sub SelectDiagonal()
a = Selection.Row()
b = Selection.Column()
c = Selection.Rows.Count
d = Selection.Columns.Count
Dim Rng As Range
Set Rng = Cells(a + c - 1, b)
For i = 1 To c - 1
Set Rng = Union(Rng, Cells(a + c - 1 - i, b + i))
Next
Rng.Select
End Sub
However, I want to select all down triangle.
What I want is to select yellow cells like following pic.
I tried this code:
Sub SelectDownTriangle()
a = Selection.Row()
b = Selection.Column()
c = Selection.Rows.Count
d = Selection.Columns.Count
For i = 1 To c - 1
For j = 1 To i
Cells(a + i, b + d - j).Select
Next
Next
End Sub
But it doesn't work.
Any help? Thanks
[Edit]
With @Vityata's answer, I edit the code:
Sub SelectDownTriangle()
Dim a As Long, b As Long, c As Long, d As Long
Dim i As Long
a = Selection.Row()
b = Selection.Column()
c = Selection.Rows.Count
d = Selection.Columns.Count
Dim lastCol As Long
Dim Rng As Range
Set Rng = Cells(a + c - 1, b + 1)
For i = 1 To c - 1
Set Rng = Union(Rng, Cells(a + c - 2 - i, b + i + 1))
If Cells(a + c - 2 - i, b + i + 1).Column > lastCol Then
lastCol = Cells(a + c - 2 - i, b + i).Column
End If
Next
Dim colCounter As Long
Dim myCell As Range
For Each myCell In Rng
For colCounter = myCell.Column To lastCol
Cells(myCell.Row, colCounter).Interior.Color = vbGreen
Next colCounter
Next myCell
End Sub
However, the output is not correct.
Upvotes: 0
Views: 474
Reputation: 43595
With the usage of your code, you need a loop, that loops for every cell in the rng
to the last column possible and filling it with color:
Option Explicit
Sub SelectDiagonal()
Dim a As Long, b As Long, c As Long, d As Long
Dim i As Long
a = Selection.row()
b = Selection.Column()
c = Selection.Rows.Count
d = Selection.Columns.Count
Dim lastCol As Long
Dim Rng As Range
Set Rng = Cells(a + c - 1, b)
For i = 1 To c - 1
Set Rng = Union(Rng, Cells(a + c - 1 - i, b + i))
If Cells(a + c - 1 - i, b + i).Column > lastCol Then
lastCol = Cells(a + c - 1 - i, b + i).Column
End If
Next
Dim colCounter As Long
Dim myCell As Range
For Each myCell In Rng
For colCounter = myCell.Column To lastCol
Cells(myCell.row, colCounter).Interior.Color = vbGreen
Next colCounter
Next myCell
End Sub
Upvotes: 1