curmonth = 7
Range("A1").Select
lastrow = ActiveSheet.UsedRange.Rows.Count
For i = 2 To lastrow
ActiveSheet.Cells(i, 5).Select
ActiveSheet.Cells.NumberFormat = "mm/dd/yyyy"
'excelmonth is the date of the cell
excelmonth = ActiveSheet.Cells(i, 5)
'managed to capture the month
extractmonth = Month(excelmonth)
If extractmonth = curmonth Then
ActiveSheet.Cells.NumberFormat = "mm/dd/yyyy"
Else
'to extract the date and year of excelmonth
extractdate = Day(excelmonth)
extractyear = Year(excelmonth)
'create a new date value
Dim newdate As Date
newdate = DateSerial(extractyear, extractdate, extractmonth)
'write the new date back to cell
ActiveSheet.Cells(i, 5).Value = newdate
ActiveSheet.Cells.NumberFormat = "mm/dd/yyyy"
End If
Next i
End Sub
Bookmarks