cell color change by another cell value if there is even one "daleyed"

[negarshaban]

i have a calender in sheet6 and database is sheet1

there is one mistake,

i want if there is even one "delayed" on sheet1.column "i" , change cell color (matched data) in sheet6 to red

and if all was "completed",cell color change to green

and if there is no delayed and all is not completed

,cell color change to yellow





and when change month and year from dropdowns, if it doesn't have any task, return the background-color

Private Sub Worksheet_Activate()
 
    Dim sh As Worksheet, wInt As Variant
    Dim j As Long
    Dim row1 As Integer
    Dim cl1 As Integer
    Set sh = Sheet3
    For row1 = 5 To 15
    For cl1 = 2 To 8
    wInt = Cells(row1, cl1).Value
    
    For j = 2 To sh.Range("c" & Rows.Count).End(xlUp).Row
      If sh.Range("c" & j).Value = wInt Then
         If sh.Range("i" & j).Value = "Completed" Then
       Cells(row1, cl1).Offset(1, 0).Interior.Color = vbGreen
            Else
              Cells(row1, cl1).Offset(1, 0).Interior.Color = vbYellow
               End If
             End If
    Next
    Next
    Next
  

End Sub

 

[DanteAmor]

I don't understand when it should change to yellow. Maybe if you explain to me with a couple of examples and I adjust it in the code.
Try the following:

Private Sub Worksheet_Activate()
  Dim sh As Worksheet, wInt As Variant, r As Range
  Dim row1 As Integer, cl1 As Integer
  Dim wCount1 As Long, wCount2 As Long, wCount3 As Long
  
  Set sh = Sheet3
  Set r = sh.Range("C2:C" & sh.Range("C" & Rows.Count).End(xlUp).Row)
  
  For row1 = 5 To 15 Step 2
    For cl1 = 2 To 8
      wInt = Cells(row1, cl1).Value
      Cells(row1, cl1).Offset(1, 0).Interior.Color = xlNone
      wCount1 = WorksheetFunction.CountIf(r, wInt)
      wCount2 = WorksheetFunction.CountIfs(r, wInt, r.Offset(, 6), "Completed")
      wCount3 = WorksheetFunction.CountIfs(r, wInt, r.Offset(, 6), "Delayed")
      Select Case True
        Case wCount1 = 0
        Case wCount1 = wCount2  'all was "completed"
          Cells(row1, cl1).Offset(1, 0).Interior.Color = vbGreen
        Case wCount1 = wCount3  'cell color change to yellow
          Cells(row1, cl1).Offset(1, 0).Interior.Color = vbYellow
        Case Else               'is even one "delayed"
          Cells(row1, cl1).Offset(1, 0).Interior.Color = vbRed
      End Select
    Next
  Next
End Sub

 

[negarshaban]

yeeeeeeeeeeeeeeeeeee

I don't understand when it should change to yellow. Maybe if you explain to me with a couple of examples and I adjust it in the code.
Try the following:

Private Sub Worksheet_Activate()
  Dim sh As Worksheet, wInt As Variant, r As Range
  Dim row1 As Integer, cl1 As Integer
  Dim wCount1 As Long, wCount2 As Long, wCount3 As Long
 
  Set sh = Sheet3
  Set r = sh.Range("C2:C" & sh.Range("C" & Rows.Count).End(xlUp).Row)
 
  For row1 = 5 To 15 Step 2
    For cl1 = 2 To 8
      wInt = Cells(row1, cl1).Value
      Cells(row1, cl1).Offset(1, 0).Interior.Color = xlNone
      wCount1 = WorksheetFunction.CountIf(r, wInt)
      wCount2 = WorksheetFunction.CountIfs(r, wInt, r.Offset(, 6), "Completed")
      wCount3 = WorksheetFunction.CountIfs(r, wInt, r.Offset(, 6), "Delayed")
      Select Case True
        Case wCount1 = 0
        Case wCount1 = wCount2  'all was "completed"
          Cells(row1, cl1).Offset(1, 0).Interior.Color = vbGreen
        Case wCount1 = wCount3  'cell color change to yellow
          Cells(row1, cl1).Offset(1, 0).Interior.Color = vbYellow
        Case Else               'is even one "delayed"
          Cells(row1, cl1).Offset(1, 0).Interior.Color = vbRed
      End Select
    Next
  Next
End Sub

yeeeeeeeeeeeeees.
it's worked.
you are so professional.

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.