NumberFormat based on Date

[jbrouse]

Can someone please assist me in writing a For/If statement that will look at each cell in every other row in range K10:AV48, and format it based on the following conditions:

If the date in each cell is the current year, format it as m/d, but if the date is next year or beyond, format the number as m/yy.

Any assistance would be appreciated. Thanks!

 

[MichaelRo]

Try This:

Sub FormatDate()

Dim C As Range
Dim irow As Long
Dim icol As Long

For irow = 10 To 48 Step 2
    For icol = 11 To 48
        Set C = Cells(irow, icol)
            
        If Year(C) = Year(Date) Then
            C.NumberFormat = "M/D"
        Else
            C.NumberFormat = "M/YY"
        End If
    Next icol
Next irow

End Sub

Whoops...

 

[Joe4]

Michael,

Since they want every other row, I think you meant to say:

For irow = 10 To 48 Step 2

instead of

For irow = 10 To 48 Step 1

 

[jbrouse]

Works great. Thanks!

 

[Oorang]

No, step 1 would hit every one.

Sub ExampleCode()
Dim MyRng As Range
Dim MyCll As Object
Set MyRng = Range("K10:AV48")
For Each MyCll In MyRng
Application.StatusBar = MyCll.Address & Chr(45) & Year(CDate(MyCll.Value))
'Check to see to if row is even. (If you want to check if odd, change 2 to 1.)
    If MyCll.Row Mod 2 = 0 Then
        If IsDate(MyCll.Value) Then
            If Year(CDate(MyCll.Value)) < 2006 Then
                MyCll.NumberFormat = "m/d"
                Else
                MyCll.NumberFormat = "m/yy"
                End If
            MyCll.Value = CDate(MyCll.Value)
        End If
    End If
Next MyCll
Application.StatusBar = False
End Sub