Sub kumar()
Dim a As Long, c As Long, d As Long
c = Range("B65536").End(xlUp).Row
For a = 2 To c
If Weekday(Cells(a, 2)) = 7 Or Weekday(Cells(a, 2)) = 1 Then
Cells(a, 2).Interior.ColorIndex = 3
Cells(a, 3).Interior.ColorIndex = 3
End If
Next a
For d = 2 To Range("Z65536").End(xlUp).Row
Cells(1, 26) = "=match(Z" & d & ",B2:B" & c & ",0)"
Cells(1, 27) = "=iserror(Z1)"
If Cells(1, 27) = False Then
Cells(Cells(1, 26) + 1, 2).Interior.ColorIndex = 3
Cells(Cells(1, 26) + 1, 3).Interior.ColorIndex = 3
End If
Next d
MsgBox " complete."
End Sub
| Excel Workbook | ||||
|---|---|---|---|---|
| H | I | |||
| 8 | 12/13/2009 | Sun | ||
| 9 | 12/14/2009 | Mon | ||
| 10 | 12/15/2009 | Tue | ||
| 11 | 12/16/2009 | Wed | ||
| 12 | 12/17/2009 | Thu | ||
| 13 | 12/18/2009 | Fri | ||
| 14 | 12/19/2009 | Sat | ||
Sheet3 | ||||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| H8 | 1. / Formula is =NOT(ISNA(MATCH($H8,holidays,0))) | Abc | ||
| H8 | 2. / Formula is =OR($I8="Sun",$I8="Sat") | Abc | ||
| I8 | 1. / Formula is =NOT(ISNA(MATCH($H8,holidays,0))) | Abc | ||
| I8 | 2. / Formula is =OR($I8="Sun",$I8="Sat") | Abc | ||
