Hi everyone, I am having difficulty in getting this code right. If you look at my screenshot, From columns L to T , I will have rows of data of leaves being applied by employees. They will have multiple rows of different leave types as they apply leave (from column L to T). From column W onwards, I will press the command button "Transfer Now" and it will spill out the dates horizontally without including weekends and holidays. I have gotten this code right.However, I need to write in a code to check from column W onwards only (in terms of row) to check the dates applied by the employee for different leave types on whether there is any duplicate date picked. For eg, if you can see in the screenshot, employee A has applied same dates like (22/12/2022) for VL (Cell X2) and SBL (Cell W3). I want the code to highlight the duplicate values for the respective employee. Can someone help me on this?
VBA Code:
Sub checkforrepeateddates()
'Declare All Variables.
Dim myCell As Range
Dim myRow As Integer
Dim myRange As Range
Dim myCol As Integer
Dim i As Integer
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Lee Hyunsoo (3)")
With ws
myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count
myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count
'Loop Each Row To Check Duplicate Values and Highlight cells.
For i = 2 To myRow
Set myRange = Range(Cells(i, 2), Cells(i, myCol))
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 3
End If
Next
Next
End With
End Sub
