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!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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:
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,216,099
Messages
6,128,820
Members
449,469
Latest member
Kingwi11y

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top