Rolled up color coding

[kleinmat]

Hi all,

I have an Excel Sheet that is used for task planning.

It is broken into SECTIONS (that I just called Team 1 and Team 2 here).
In each section, there are TASKS.
And under each Task, there are ENTRIES.

I just used entries a-h and just two sections with three tasks but the real sheet is over 10,000 lines long and 100 columns wide.

What I am TRYING to achieve:
Whenever a certain column under a task has an entry, I want the cell in the same column but on the level of the "Task description" to be color-marked. For example, in the example, C7 = x, so I want C6 to be color-filled because line 6 is where "Task 1" is described.

Like so.
I HAVE found a solution using just Excel Formula.
Basically, I am using a bunch (over 100) hidden helper-columns.

The first one just contains a formula that writes "x" into the cell if the corresponding cell in Column B contains an entry. So x means: there is a task description on this line.

The other ones basically contain a formula that says this:

if this line contains a task description (if Task-Helper = x)

     then

          if the cell below = x

               then

                    write "c" into this cell

               else

                    write "" into this cell (keep it blank)

     else

          if the cell below = x

               then

                    write "x" into this cell (this basically rolls up the x-marker all the way until the formula 'hits' a line with a task description

               else

                    if the entry-cell corresponding to this cell is empty (there is no entry) or if it just contains the value 0

                         then

                              write "" into this cell (keep it blank)

                         else

                              write "x" into this cell (this means: there is actually an entry like a-h in the corresponding cell)

Like so

And then all I have to do is use conditional formatting to mark the cells in columns C-F blue, if the corresponding helper-cell = C (C = please color-fill).

While this insanity works, it has three major flaws:
1. Given the Excel is ACTUALLY over 10000 rows long and over 100 columns wide, just the formulas alone increase the size of the empty excel from 1MB to 28MB
2. It is incredibly slow
3. Oh boy is this solution ugly, it hurts by heart

Can someone PLEASE show me a more elegant solution? This Excel is macro-enabled as it already contains some VBA code that some very kind person in this forum suggested.

Thank you
Matt

 

[myall_blues]

Would this help?

Sub Shading()
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long, k As Long
    Dim task1 As Variant, task2 As Variant
    With Application
        .EnableEvents = False
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
        lastRow = Worksheets("Sheet1").Cells.Find("*", _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lastCol = Worksheets("Sheet1").Cells.Find("*", _
                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    task2 = ""
    For i = 5 To lastRow
        task1 = Cells(i, 2).Value
        k = i + 1
        If task1 <> "" Then
            Do While task2 = "" And k <= lastRow
                task2 = Cells(k, 2).Value
                For j = 3 To lastCol
                    If Cells(k, j).Value <> "" Then
                        Cells(i, j).Interior.Color = RGB(33, 92, 152)
                    End If
                Next j
                k = k + 1
            Loop
            task2 = ""
        End If
    Next i
    With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

 

[felixstraube]

Another option with CF formula:
I assumed that you would not have more than 30 rows between tasks. We could adjust the formula to your real data. Let me know and I correct it.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5	Team 1
6		Task 1
7			x	y
8			z		w
9
10		Task 2
11				a
12				b	c
13					d
14
15	Team 2
16		Task 3
17				e
18					f
19				g		h
20
21
22
23
24
25
26
27
28
29
30
31
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:K34	Expression	=IF($B6<>"", LET(t, $B7:$B36, i, IFERROR(INDEX(MATCH(TAKE(TOCOL(t, 1), 1), t, 0)-1,1, 1), 30), e, TAKE(C7:C36, i), SUM((e<>"")*1) ))	text	NO

 

[felixstraube]

Another formula option:
(not sure which one is more efficient. I guess this last one. Try it out on your full data set)

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5	Team 1
6		Task 1
7			x	y
8			z		w
9
10		Task 2
11				a
12				b	c
13					d
14
15	Team 2
16		Task 3
17				e
18					f
19				g		h
20
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:K34	Expression	=IF($B6<>"",IFERROR(MATCH(TRUE,C7:C36<>"", 0), 31)<IFERROR(MATCH(TRUE,$B7:$B36<>"", 0), 31))	text	NO