Trying to get my for loop to go diagonally rather than filling in every cell-CodePudding

I am having trouble getting my for loop to fill in the correct cells and I am hoping someone can help me understand how to only fill certain cells.

Currently, I have a list of names in a sheet called "3 - Task Listing" and I am using an array formula to pull the list of names into my "5 - Task Compatibility" sheet starting in cell B11 and going down the column and D9 going across that row.

So, if there are two names on the list, Clinic AM and Clinic PM - Clinic AM would be in cells D9 and B11. What I am hoping to achieve with my macro is having it fill in the cell where those meet up, D11, turn a dark grey and input the name in that cell. Then moving over one column and down one row (cell E12) would then also be dark grey and would say Clinic PM. It would continue on like this based on the number of names creating a diagonal line of dark grey cells with the names from the list in them.

The code I have below has the column length hard coded as 2 for now so I will need to adjust that to look at the length of the list of names.

The other issue I am having is it did fill in cells D11:E12 with the names and turned them dark grey. However, I need it to only change the cells on the diagonal line so cells E11 and D12 should be blank but are also turning grey and having the name from the list inserted into them.

If someone could help me with making my for loop move on the diagonal rather than filling every cell in the range that would be greatly appreciated! Thanks!

 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
 CL = 2

 For i = 11 To 10   CL
 For y = 4 To 3   CL

 With Worksheets("5 - Task Compatibility").Cells(i, y)
      .Interior.Color = RGB(150, 150, 150)
      .Borders.ColorIndex = 48
 End With

 Worksheets("5 - Task Compatibility").Cells(i, y) = Worksheets("5 - Task 
 Compatibility").Range("B" & i).Value

 Next y
 Next i

 End Sub

CodePudding user response：

if i understood you:

cl = 2
Dim StartY
StartY = 9
For i = 11 To 10   cl
 With Worksheets("5 - Task Compatibility").Cells(i, StartY)
      .Interior.Color = RGB(150, 150, 150)
      .Borders.ColorIndex = 48
      .Value = Worksheets("5 - Task Compatibility").Range("B" & i).Value
 End With
StartY = StartY   1
 Next i

CodePudding user response：

Pretty much the same as @Red Hare but I chose to define a separate variable, k, for the loop. The main point is that there is only a single loop, not nested loops, with both i and y incrementing by one for each time round the loop.

Option Explicit

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
     
     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
        
         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