Excel conditional format when all tasks mentioned in one cell are completed

Question

I have a TODO list in excel where all tasks are categorized and have a unique ID (1.1, 1.2, 2.1, etc.). Some tasks require one or more other tasks to be completed (ie. 1.4 can only start when 1.1, 1.2, 1.3 and 2.5 are completed). The user can add his own criteria as to which tasks must be finished. This can be any combination of tasks.

Table information: Column A - ID (1.1; 1.2; 2.1; 2.2)

Column H - Status (where "V" represents the checked symbol, meaning it is finished)

Column K - Pre-condition (user input like "3.1, 2.1, 4.5" as one text value)

I want to test each ID in the cell (which can be none to infinite) and it's corresponding status and format the cells if all tasks written in the cell are completed.

Does anyone know a good way to do this?

The picture shows a summary of the the tasklist. you see that in row 5 (ID 1.2) the cell in column K is green because the stated ID was completed. Row 27 (ID 3.2) should show a green value when both task 2.3 and 3.1 are completed (aka have a "V" in column H. The amount of pre-conditions may vary per task.

Image of desired result

Can you include a screenshot of your actual spreadsheet and an example desired result? — ImaginaryHuman072889

Gordon Gordon · Accepted Answer · 2017-10-12T18:07:56

The first part is pretty simple as the formula below will work for K5, K6, and K15 when entered as a conditional format for column K.

=countifs($A$1:$A$1000,$K1,$H$1:$H$1000,"v")>0

Checking the cells with multiple tasks is more difficult and you may need to split them into cells in hidden columns to check them.

You could check the cells with multiple task with a custom VBA function. Add the function to a VBA module and save then use the formula =canStart(K1)=True in a conditional formatting in column K.

This will check unlimited subtasks but will not update automatically so you will need to use Ctrl+Alt+F9 periodically to update.

Function canStart(Rng As Range) As Variant
Dim arrSubtasks As Variant
Dim subTasksComplete As Boolean

If Rng.Value <> "" Then 'check if there is a subtask in the cell
    If InStr(1, Rng.Value, ",") > 0 Then ' check if there is more than 1 subtask (look for a comma)
        arrSubtasks = Split(Rng.Value, ",") ' create an array of all the subtasks in the cell
        subTasksComplete = True ' set sub tasks as complete
        For Each subTask In arrSubtasks ' loop through each subtask
            If Application.WorksheetFunction.CountIfs(Range("A2:A1000"), subTask, Range("K2:K1000"), "v") = 0 Then 'if the subtask being checked is not found as complete
                subTasksComplete = False ' mark subTasksComplete as false and do not continue to check the others (save some time)
                Exit For
            End If
        Next
    Else ' if there is only one subtask then check if it's complete
        subTasksComplete = True
        If Application.WorksheetFunction.CountIfs(Range("A2:A1000"), Rng.Value, Range("K2:K1000"), "v") = 0 Then
            subTasksComplete = False
        End If
    End If
        canStart = subTasksComplete 'set the returned value to the value of subtasksComplete
End If
End Function

Excel conditional format when all tasks mentioned in one cell are completed

1 Answers