|There are a few things that are still unclear, both in the macro and in your explanation.|
1 - The comments in the code don’t match up with the instructions being executed. For example, one comment says “change the background color to red“, but the instruction that follows changes the cell to Yellow. Another says to clear the background, but the instruction that follows changes the cell to Green. Other comments say that the code is checking the cell for Red, when the instruction is actually checking for Yellow or Green. What’s up with all the comments that don’t match the instructions?
2 – You posted a SelectionChange macro with no instructions in it. What is the purpose of that? If it is just hanging around in the sheet module then you should delete it. Even though there are no instructions it in, Excel is going to attempt to run it each and every time you select a cell in that sheet. That’s a waste of resources.
3 – You didn’t say what cell you want to turn Green when all tasks are completed (i.e. Green). For the purposes of this exercise, I am going to use the cell in Row 43.
That said, try the code that I have posted below. How the code works is as follows:
1 - Each time a cell within the Tasks Table is double-clicked, the first part of the code will toggle the fill color from None to Yellow to Green to None.
2 – The second part of the code will count all of the Task Cells in the Target column (those cells that are not Black) and also count all cells that are Green in that same column.
3 - If the count of Task Cells equals the count of Green Cells, then all Tasks are marked as complete. If that is the case, then the cell in Row 43 will read “All Complete” and become green. If not, the cell in Row 43 will read “Not Complete” and the color will be cleared. The cell in Row 43 will change to Not Complete and the color will be cleared if you "uncomplete" one or more Tasks that were previously Green. This allows for changes if you make a mistake or decide afterwards that a Task is not really complete.
One other note: In the original code, double clicking any cell - even if it was outside of the Tasks Table - would toggle the color of the cell. I have added an instruction to only have the colors changed within the Tasks Table itself.
Let me know how it works for you.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' This subroutine toggles the color of cell from Clear to Yellow to Green to Clear
' with each Double-Click. It then counts the number of Green cells in a Column and
' turns the Completed task cell Green if all tasks are complete.
'Ensure selected cell is within Task Table
Select Case Target.Column
Case 3, 5, 7, 9, 11, 13, 15
Select Case Target.Row
Case 4 To 34
' If the cell is clear
If Target.Interior.ColorIndex = xlNone Then
' Then change the background color to Yellow
Target.Interior.ColorIndex = 36
' Else if the cell background color is Yellow
ElseIf Target.Interior.ColorIndex = 36 Then
' Then change the background color to Green
Target.Interior.ColorIndex = 35
' Else if the cell background color is Green
ElseIf Target.Interior.ColorIndex = 35 Then
' Then clear the background
Target.Interior.ColorIndex = xlNone
' This is to prevent the cell from being edited when double-clicked
Cancel = True
'Loop through cells in Rows 4 - 34)
For Each cell In Range(Cells(4, Target.Column), Cells(34, Target.Column))
'Count Task Cells (non-Black Cells)
If cell.Interior.ColorIndex <> 1 Then task_cell = task_cell + 1
'Count Green cells
If cell.Interior.ColorIndex = 35 Then green_cell = green_cell + 1
'If count of Task cells = count of Green cells, then Row 43 cell should be green
'Row 43 cell should be Clear otherwise
If task_cell = green_cell Then
Cells(43, Target.Column).Interior.ColorIndex = 35
Cells(43, Target.Column) = "All Complete"
Cells(43, Target.Column).Interior.ColorIndex = xlNone
Cells(43, Target.Column) = "Not Complete"
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.