How to Nest a For Loop to Color all Cells White Below the Diagonal-CodePudding

Workbook Breakdown:

I have two sheets: "3 - Task Listing" and "5 - Task Compatibility"

In the "3 - Task Listing" sheet there is a list of Task Names in Column B starting in cell 15.

In the "5 - Task Compatibility" sheet I am pulling the list of names from "3 - Task Listing" using an array formula into a row starting in Cell D9 and a column starting in Cell B11.

So, where those two lists match up Cell D11,E12,F13, etc. I have it filling those cells dark grey and writing the name from the list creating a diagonal line of greyed out cells. That is working perfectly. However, what I still need to do is to make it turn all of the cells below that diagonal white down to the last row with a Task name in Column B. Currently, the code below is turning the entire column B below the diagonal white because the array formula is causing there to be a 0 in each row past the last cell with a value.

Additionally, I need to find a way to nest another loop so that it then turns the cells white in each column below the diagonal not just Column B.

Also, I have the CL variable looking at the list of names in the "3 - Staff Listing" sheet and the list stops after Clinic 5. However, it seems to be adding zeros to the end and I am not sure why?

The first image below is what the Code is currently doing. The image after is what I am trying to achieve.

If anyone has any suggestions about how to modify this code to achieve the second picture I would really appreciate the help. I feel like I am so close but I just can't quite get this last part to work.

 Sub Gen_Grid()

 Worksheets("5 - Task Compatibility").Range("D11:EZ1000").ClearContents

 With Worksheets("5 - Task Compatibility").Range("D11:EZ1000")
     .Interior.Color = RGB(192, 192, 192)
     .Borders.ColorIndex = 15
 End With

 Dim CL As Long, i As Integer, y As Integer, k As Integer

 CL = Worksheets("3 - Task Listing").Range("B15").End(xlDown).Row

 
 i = 11
 y = 4
 
 For k = 1 To CL
    
     With Worksheets("5 - Task Compatibility").Cells(i, y)
          .Interior.Color = RGB(150, 150, 150)
          .Borders.ColorIndex = 48
     End With
     
    With Worksheets("5 - Task Compatibility").Range("D12:D" & Cells(Rows.count, 2).End(xlUp).Row)
          .Interior.Color = RGB(255, 255, 255)
          .Borders.ColorIndex = 48
          .FillDown
     End With
    
    
     Worksheets("5 - Task Compatibility").Cells(i, y) = Worksheets("5 - Task Compatibility").Range("B" & i).Value
     
     i = i   1
     y = y   1
    
 Next k

 End Sub

CodePudding user response：

It's getting late here so this may not be the best code but basically you do need a nested loop now so it should be like this:

Sub Gen_Grid()

 Worksheets("5 - Task Compatibility").Range("D11:EZ1000").ClearContents

 With Worksheets("5 - Task Compatibility").Range("D11:EZ1000")
     .Interior.Color = RGB(192, 192, 192)
     .Borders.ColorIndex = 15
 End With

 Dim CL As Integer, i As Integer, y As Integer, k As Integer, j As Integer, y1 As Integer
 
 CL = 2
 
 i = 11
 y = 4
 
 For k = 1 To CL
    
     With Worksheets("5 - Task Compatibility").Cells(i, y)
          .Interior.Color = RGB(150, 150, 150)
          .Borders.ColorIndex = 48
     End With
     
     y1 = 4
     
     For j = 1 To k
     
        With Worksheets("5 - Task Compatibility").Cells(i   1, y1)
           .Interior.Color = RGB(255, 255, 255)
           .Borders.ColorIndex = 48
        End With
     
        y1 = y1   1
     
     Next j
    
     Worksheets("5 - Task Compatibility").Cells(i, y) = Worksheets("5 - Task Compatibility").Range("B" & i).Value
     
     i = i   1
     y = y   1
    
 Next k

End Sub