NumberFormat based on Date

jbrouse

Active Member
Joined
Apr 28, 2005
Messages
329
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!
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

MichaelRo

Well-known Member
Joined
Jun 7, 2004
Messages
549
Try This:

Code:
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... :LOL:
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows
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
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
No, step 1 would hit every one.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,795
Messages
5,574,352
Members
412,588
Latest member
FabrizioMaurizio
Top