Macro help: To Show the dependant row under the main Row

[gsaini]

This is regarding my Xl sheet where I am updating the sheet using the SQL query. There are columns from A to N which are populated with useful data and then Column O and P with MainTask ID and Task id.

Basically data contains two different Rows; one is main task and second is DEPENDANT Task.

My requirement is to show the dependant tasks under the main task for which I am using the relationship between Maintask ID (Column O) and Task ID (Column P). I have pasted my code which I am using to achieve my requirement.

Also changing the colour of Dependant tasks to BLUE.

Most of the time code seems to be working but sometime it misses the Dependant rows and shows the row as Main Task.


Dim previousRowNo As Integer
Dim cellAddress

Public Sub CellWrite(nodedName As String, nodeValue As String, noOfMilestone As Integer)



cellAddress = Split(shortName, ",")
'If there are more than four milestone then this will add one more row
If (noOfMilestone = CInt(cellAddress(0)) + 4) And addRowFlag = False Then
Sheets(scheduleSheet$).Activate
Rows(noOfMilestone - 1).Copy
Rows(noOfMilestone).Insert
Rows(noOfMilestone).value = ""
cellAddress = Split(milestoneRAG, ",")
Cells(noOfMilestone, CInt(cellAddress(1))).Select
With Selection.Interior
.colorIndex = "2"
.Pattern = xlSolid
End With
previousRowNo = noOfMilestone
addRowFlag = True
Sheets(scheduleSheet$).Activate
ElseIf (noOfMilestone >= CInt(cellAddress(0)) + 4) And previousRowNo <> noOfMilestone Then
Sheets(scheduleSheet$).Activate
Rows(noOfMilestone - 1).Copy
Rows(noOfMilestone).Insert
Rows(noOfMilestone).value = ""
cellAddress = Split(milestoneRAG, ",")
Cells(noOfMilestone, CInt(cellAddress(1))).Select
With Selection.Interior
.colorIndex = "2"
.Pattern = xlSolid
End With
previousRowNo = noOfMilestone
addRowFlag = False
Sheets(scheduleSheet$).Activate

End If

Select Case nodedName

Data is generated here from SQL Query

End Select
End Sub

'** This method colors the external dependent milestones to distinguish them from mail milestones of choosen plan. This will also insert the dependent milestone below main milestone
Public Sub hideColumn()

Dim lastRow As Integer
lastRow = Range("E65536").End(xlUp).row
Dim rowCounter As Integer
Dim rowCounterI As Integer
Dim mainTaskid As String
Dim row As Integer
Dim outerColumn As Integer
Dim innerColumn As Integer


Columns("O:P").Hidden = False
cellAddress = Split(mainTask, ",")
row = CInt(cellAddress(0))
outerColumn = CInt(cellAddress(1))

cellAddress = Split(taskId, ",")
innerColumn = CInt(cellAddress(1))

completMileStoneFormatingFlag = False


On Error GoTo milestoneError​

For rowCounter = row To lastRow
'If it is a dependent task then mainTaskid is task id if main task. If it is maintask then this id will be blank
mainTaskid = Sheets(scheduleSheet$).Cells(rowCounter, outerColumn).value
If mainTaskid <> "" Then
For rowCounterI = row To lastRow
'This will insert the dependent task below main task and color the dependent task
If Sheets(scheduleSheet$).Cells(rowCounterI, innerColumn).value = mainTaskid And Sheets(scheduleSheet$).Cells(rowCounterI, outerColumn).value = "" Then
If (rowCounter <> rowCounterI + 1) Then
Rows(rowCounter).Cut
Rows(rowCounterI + 1).Insert Shift:=xlDown
End If

If rowCounterI + 1 > rowCounter Then
Range(Sheets(scheduleSheet$).Cells(rowCounterI, 2), Sheets(scheduleSheet$).Cells(rowCounterI, 10)).Interior.colorIndex = 34
If Cells(rowCounterI, 11).value = "" Then
Range(Sheets(scheduleSheet$).Cells(rowCounterI, 11), Sheets(scheduleSheet$).Cells(rowCounterI, 14)).Interior.colorIndex = 34
Else
Range(Sheets(scheduleSheet$).Cells(rowCounterI, 12), Sheets(scheduleSheet$).Cells(rowCounterI, 14)).Interior.colorIndex = 34
End If
Else

Range(Sheets(scheduleSheet$).Cells(rowCounterI + 1, 2), Sheets(scheduleSheet$).Cells(rowCounterI + 1, 10)).Interior.colorIndex = 34
If Cells(rowCounterI + 1, 11).value = "" Then
Range(Sheets(scheduleSheet$).Cells(rowCounterI + 1, 11), Sheets(scheduleSheet$).Cells(rowCounterI + 1, 14)).Interior.colorIndex = 34
Else
Range(Sheets(scheduleSheet$).Cells(rowCounterI + 1, 12), Sheets(scheduleSheet$).Cells(rowCounterI + 1, 14)).Interior.colorIndex = 34
End If
End If

Exit For

End If
Next
​

End If
Next

Columns("O:P").Hidden = True
completMileStoneFormatingFlag = True

Exit Sub
milestoneError:
Exit Sub
End Sub



​

​