Peter Chen
Peter Chen

Reputation: 1484

excel vba select right down triangle

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.

enter image description here

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.
enter image description here

What I want is:
enter image description here

Upvotes: 0

Views: 474

Answers (1)

Vityata
Vityata

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

enter image description here

Upvotes: 1

Related Questions