Excel VBA如果条件满足则插入新行，并在条件满足时填充颜色

Option Explicit

Sub InsertRowBelowNegativeEntriesInFGHI()

    Dim lLastColRow As Long
    Dim lLastRow As Long
    Dim lColIndex As Long
    Dim lRowIndex As Long
    Dim bInsert As Boolean
    Dim bIsBalanceRow As Boolean
    Dim vFPos As Variant
    Dim vGPos As Variant
    Dim vHPos As Variant
    Dim vIPos As Variant
    Dim vJPos As Variant
    Dim vKPos As Variant
    Dim vLPos As Variant
    Dim vMPos As Variant
    Dim vNPos As Variant
    Dim vOPos As Variant
    Dim vPPos As Variant
    Dim vQPos As Variant
    Dim vRPos As Variant
    Dim vSPos As Variant
    Dim vTPos As Variant
    Dim sTrigger As String

    For lColIndex = 6 To 10
        lLastColRow = Cells(Rows.Count, lColIndex).End(xlUp).Row
        If lLastColRow > lLastRow Then lLastRow = lLastColRow
    Next

    For lRowIndex = lLastRow - 1 To 2 Step -1
        If UCase(Cells(lRowIndex, 1).Value) = "BALANCE" Then
            'On a BALANCE row
            bInsert = False
            vFPos = Cells(lRowIndex, "F").Value
            vGPos = Cells(lRowIndex, "G").Value
            vHPos = Cells(lRowIndex, "H").Value
            vIPos = Cells(lRowIndex, "I").Value
            vJPos = Cells(lRowIndex, "J").Value

            If vFPos < 0 And (vGPos > 0 Or vHPos > 0 Or vIPos > 0 Or vJPos > 0) Then bInsert = True: 'sTrigger = "F"
            If vGPos < 0 And (vHPos > 0 Or vIPos > 0 Or vJPos > 0) Then bInsert = True: 'sTrigger = "G"
            If vHPos < 0 And (vIPos > 0 Or vJPos > 0) Then bInsert = True: 'sTrigger = "H"
            If vIPos < 0 And (vJPos > 0) Then bInsert = True: 'sTrigger = "I"

            If bInsert Then
                Cells(lRowIndex + 1, 1).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
                'Debug.Print lRowIndex, sTrigger
            End If

        End If
    Next
    For lColIndex = 11 To 15
        lLastColRow = Cells(Rows.Count, lColIndex).End(xlUp).Row
        If lLastColRow > lLastRow Then lLastRow = lLastColRow
    Next

    For lRowIndex = lLastRow - 1 To 2 Step -1
        If UCase(Cells(lRowIndex, 1).Value) = "BALANCE" Then
            'On a BALANCE row
            bInsert = False
            vKPos = Cells(lRowIndex, "K").Value
            vLPos = Cells(lRowIndex, "L").Value
            vMPos = Cells(lRowIndex, "M").Value
            vNPos = Cells(lRowIndex, "N").Value
            vOPos = Cells(lRowIndex, "O").Value

            If vKPos < 0 And (vLPos > 0 Or vMPos > 0 Or vNPos > 0 Or vOPos > 0) Then bInsert = True: 'sTrigger = "K"
            If vLPos < 0 And (vMPos > 0 Or vNPos > 0 Or vOPos > 0) Then bInsert = True: 'sTrigger = "L"
            If vMPos < 0 And (vNPos > 0 Or vOPos > 0) Then bInsert = True: 'sTrigger = "M"
            If vNPos < 0 And (vOPos > 0) Then bInsert = True: 'sTrigger = "N"

            If bInsert Then
                Cells(lRowIndex + 1, 1).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
                'Debug.Print lRowIndex, sTrigger
            End If

        End If
    Next
    For lColIndex = 16 To 20
        lLastColRow = Cells(Rows.Count, lColIndex).End(xlUp).Row
        If lLastColRow > lLastRow Then lLastRow = lLastColRow
    Next

    For lRowIndex = lLastRow - 1 To 2 Step -1
        If UCase(Cells(lRowIndex, 1).Value) = "BALANCE" Then
            'On a BALANCE row
            bInsert = False
            vPPos = Cells(lRowIndex, "P").Value
            vQPos = Cells(lRowIndex, "Q").Value
            vRPos = Cells(lRowIndex, "R").Value
            vSPos = Cells(lRowIndex, "S").Value
            vTPos = Cells(lRowIndex, "T").Value

            If vPPos < 0 And (vQPos > 0 Or vRPos > 0 Or vSPos > 0 Or vTPos > 0) Then bInsert = True: 'sTrigger = "P"
            If vQPos < 0 And (vRPos > 0 Or vSPos > 0 Or vTPos > 0) Then bInsert = True: 'sTrigger = "Q"
            If vRPos < 0 And (vSPos > 0 Or vTPos > 0) Then bInsert = True: 'sTrigger = "R"
            If vSPos < 0 And (vTPos > 0) Then bInsert = True: 'sTrigger = "S"

            If bInsert Then
                Cells(lRowIndex + 1, 1).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
                'Debug.Print lRowIndex, sTrigger
            End If

        End If
    Next
End Sub

我正在使用上面的代码来查找在余额行的FGHIJ，KLMNO，PQRST列中是否有任何负数后跟正数。在A列中有多个“余额”行。

上面的代码正常工作，并且在从左到右依次为负值和负值的情况下，在余额上方插入新行。但是，对于PQRST列（对于16-20列）而言，它不起作用，我不知道为什么，并且对此代码需要进行哪些更改？

1. 如果条件满足，我想添加2行，而不是全部3个分类列（FGHIJ）（KLMNO）（PQRST）中的1行
2. 我想在第一个空白添加行的A列中有一个单词“ By Adjustment”。
3. 我希望该部分应填充为符合条件的绿色。

例如在F6 G6 H6 I6 J6中的值为0 -10 100 0 10

此处将添加2个新行，然后用绿色填充F6 G6 H6 I6 J6。

满足条件的任何地方都应该用绿色上色，并插入两行空白。