Modify cell from "parent" row if "child" cell has negative value

[SY24]

Greetings,

We're working with parts-lists exported to xlsx format. These are huge but get "cropped" down to relevant data with a macro. Here's an example how the sheet looks like after applying the macro.

AssemblyStructure
	A	B	C	D	E
1	Level	Pos.	Part / Assy No.	Part / Assy Name	Amount
2	1.........	1	SAMPLENO	SAMPLENAME	1,000
3	.2........	1	SAMPLENO	SAMPLENAME	1,000
4	.2........	2	SAMPLENO	SAMPLENAME	2,000
5	.2........	3	SAMPLENO	SAMPLENAME	1,000
6	..3.......	1	SAMPLENO	SAMPLENAME	1,000
7	..3.......	2	SAMPLENO	SAMPLENAME	-3,000
8	..3.......	3	SAMPLENO	SAMPLENAME	-6,000
9	..3.......	4	SAMPLENO	SAMPLENAME	0,200
10	.2........	4	SAMPLENO	SAMPLENAME	1,000
11	..3.......	1	SAMPLENO	SAMPLENAME	1,000
12	..3.......	2	SAMPLENO	SAMPLENAME	2,000
13	..3.......	3	SAMPLENO	SAMPLENAME	1,000
14	.2........	5	SAMPLENO	SAMPLENAME	1,000
15
16	.2........	6	SAMPLENO	SAMPLENAME	-1,000
17	.2........	7	SAMPLENO	SAMPLENAME	2,000
18	..3.......	1	SAMPLENO	SAMPLENAME	1,000
19	..3.......	2	SAMPLENO	SAMPLENAME	1,000
20	..3.......	3	SAMPLENO	SAMPLENAME	-1,000
21	..3.......	4	SAMPLENO	SAMPLENAME	1,000
22	..3.......	5	SAMPLENO	SAMPLENAME	2,000
Sheet1

Sometimes parts on Level 3 are exchanged and hence have a negative value. I want to modify the macro so that the associated assembly on Level 2 (always above Level 3 parts, might call it a "parent" cell/row even) gets highlighted in red when at least one "child cell" has a negative value. Negative values on other "Levels" should be ignored however. The result ideally would look like this:

AssemblyStructure
	A	B	C	D	E
1	Level	Pos.	Part / Assy No.	Part / Assy Name	Amount
2	1.........	1	SAMPLENO	SAMPLENAME	1,000
3	.2........	1	SAMPLENO	SAMPLENAME	1,000
4	.2........	2	SAMPLENO	SAMPLENAME	2,000
5	.2........	3	SAMPLENO	SAMPLENAME	1,000
6	..3.......	1	SAMPLENO	SAMPLENAME	1,000
7	..3.......	2	SAMPLENO	SAMPLENAME	-3,000
8	..3.......	3	SAMPLENO	SAMPLENAME	-6,000
9	..3.......	4	SAMPLENO	SAMPLENAME	0,200
10	.2........	4	SAMPLENO	SAMPLENAME	1,000
11	..3.......	1	SAMPLENO	SAMPLENAME	1,000
12	..3.......	2	SAMPLENO	SAMPLENAME	2,000
13	..3.......	3	SAMPLENO	SAMPLENAME	1,000
14	.2........	5	SAMPLENO	SAMPLENAME	1,000
15
16	.2........	6	SAMPLENO	SAMPLENAME	-1,000
17	.2........	7	SAMPLENO	SAMPLENAME	2,000
18	..3.......	1	SAMPLENO	SAMPLENAME	1,000
19	..3.......	2	SAMPLENO	SAMPLENAME	1,000
20	..3.......	3	SAMPLENO	SAMPLENAME	-1,000
21	..3.......	4	SAMPLENO	SAMPLENAME	1,000
22	..3.......	5	SAMPLENO	SAMPLENAME	2,000
Sheet1

After this process all Level 3 parts get removed from the list, but we already have working code for that.

AssemblyStructure
	A	B	C	D	E
1	Level	Pos.	Part / Assy No.	Part / Assy Name	Amount
2	1.........	1	SAMPLENO	SAMPLENAME	1,000
3	.2........	1	SAMPLENO	SAMPLENAME	1,000
4	.2........	2	SAMPLENO	SAMPLENAME	2,000
5	.2........	3	SAMPLENO	SAMPLENAME	1,000
6	.2........	4	SAMPLENO	SAMPLENAME	1,000
7	.2........	5	SAMPLENO	SAMPLENAME	1,000
8
9	.2........	6	SAMPLENO	SAMPLENAME	-1,000
10	.2........	7	SAMPLENO	SAMPLENAME	2,000
Sheet1

I would greatly appreciate any help in regards to the problem above.

SY24

 

[Micron]

I thought I understood the logic until I saw the third pic. I figured "associated assembly on Level 2 (always above Level 3 parts," meant that C5 is coloured because A5 is followed by a 3. However, there is no level 3 after row 10 in 3rd pic. One sample (in 2nd pic) of the logic to apply never seems to be enough to me.
BTW, if you're looking for help with modifying code I think you're going to have to post it (using code tags of course)? Perhaps you are just looking for suggestions on the code logic instead.

 

[SY24]

Hello Micron,

sorry for causing confusion regarding the logic. I should have pointed it out more clearly.
The third graphic is irrelevant to the problem. Unfortunately I cannot find any way to update the original post... unless I'm not seeing it.

Adding onto your understanding:
- C5 is colored because either E7 and E8 hold a negative value
- C17 is colored because E20 holds a negative value

This image is for further clarification. Each colored squares shows an assembly (or "parent", Level 2) and its associated parts (or "children", Level 3).

In regards to code I can only share my failed attempt. There isn't really anything to build upon as of now.

' Find negative value in Column E, then search backwards and find next instance of ".2........" in Column A, then fill red
    For Each cell In Range("E:E")
        If cell.Value < 0 Then
            Range("A:A").Find(What:=".2........", SearchDirection:=xlPrevious).Activate
                With Selection.Interior
                    .Color = 255
                End With
        End If
    Next

This unfortunately only affected the bottom-most instance of ".2........", completely ignoring the negative value requirement.

SY24

 

[Peter_SSs]

Welcome to the MrExcel board!

Give this a try with a copy of your workbook.

Sub Test()
  Dim a As Variant
  Dim i As Long, j As Long
  
  Application.ScreenUpdating = False
  With Range("A1", Range("E" & Rows.Count).End(xlUp))
    a = .Value
    i = UBound(a)
    Do
      If a(i, 5) < 0 Then
        If a(i, 1) Like "..3.*" Then
          Do
            j = j + 1
          Loop Until a(i - j, 1) Like ".2.*"
          .Cells(i - j, 3).Interior.Color = 255
          i = i - j
          j = 0
        End If
      End If
      i = i - 1
    Loop Until i = 2
  End With
  Application.ScreenUpdating = True
End Sub
  End With
  Application.ScreenUpdating = True
End Sub

 

[SY24]

Hello Peter,

this does the job! Amazing!
Thank you for taking the time.

SY24

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.