Change cell formatting in VBA

[andrewb90]

Hello all,

I used a macro recorder to capture what I wanted, but when I run my code, the formatting doesn't seem to change. I'm using an If statment, and the value in the cell changes, so I know the code is working, but the formatting isnt.

    Range("L23").Select
    Selection.NumberFormat = "[$-en-US]h:mm AM/PM;@"

this one is a time

    Range("L23").Select
    Selection.NumberFormat = "m/d;@"

this one is a date


any help would be appreciated.

 

[andrewb90]

Correct, but I need the way it looks to be changed. With my cell formatted as a date, when 9:15 PM is entered all I read is 1/0. And looking at the formula it shows 1/0/1900 9:15 PM. If all my values were time or date, then it would be easy because any value would appear the same.

 

[Joe4]

9:15 PM is entered all I read is 1/0

Yes, it is important to understand how Excel stores date and times. They are actually both numbers, and every valid date or time entry has both date and time components.
Basically, Excel stores Dates/Times as the number of days since 1/0/1900. The date portion is a whole number, and the time component is the decimal.
If you enter a date with no time, it just assumes that the time (decimal) part is 0.
If you enter a time with no date, it just assumes that the date (whole number) part is 0.
To see this in action, enter a few dates and times, and then change their format to General. Then you will see them as Excel sees them.

So as long as your entries have just a date component, or just a time component (and never both), something like this should work:

    If Sheets("PDFPage").Range("L23") >= 1 Then
        Sheets("PDFPage").Range("L23").NumberFormat = "m/d;@"
    Else
        Sheets("PDFPage").Range("L23").NumberFormat = "[$-en-US]h:mm AM/PM;@"
    End If