negarshaban
New Member
- Joined
- Dec 29, 2018
- Messages
- 18
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
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
VBA Code:
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