#### Jyotirmaya

##### Board Regular

- Joined
- Dec 2, 2015

- Messages
- 168

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

I am using the above code to find if there are any negetive value is followed by any positive value in the column FGHIJ,KLMNO,PQRST of Balance row. In column A there are multiple Balance rows.

The above code is working and it inserts a new row in above Balance when there are any negative value followed by any positive value from left to right. But for the column PQRST its not working, I dont know why & what should be the change required in the code for that ?

1. I want to add 2 rows if the condition meets instead of 1 rows in all 3 categorised columns (FGHIJ) (KLMNO) (PQRST)

2. I want to have a word "By Adjustment" in Column A in the first blank added row.

3. I want the portion should be filled in color green for which the condition met.

for example in F6 G6 H6 I6 J6

values are 0 -10 100 0 10

here 2 new row will be added

then F6 G6 H6 I6 J6 should be filled in with color green.

where ever the condition will be meet should be colored with green and two blank row should be inserted.