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.

 

[Joe4]

A few things:
- Changing the number format only works on numeric entries. If you have an entry that was entered as text, it will have no affect on it.
- I think it may be helpful to see the rest of your code surrounding this.

 

[andrewb90]

The cell is L23:

If Sheets("Period" & P).Range("R" & R).Value = "yes" Then    Sheets("PDFPage").Range("L23").Value = Sheets("Period" & P).Range("S" & R).Value
    Sheets("PDFPage").Range("J22").Value = Sheets("Period" & P).Range("R" & R).Value
    Sheets("PDFPage").Range("L22").Value = "Finish Time:"
    Range("L23").Select
    Selection.NumberFormat = "[$-en-US]h:mm AM/PM;@"
    
Else
    Sheets("PDFPage").Range("J22").Value = Sheets("Period" & P).Range("R" & R).Value
    Sheets("PDFPage").Range("L22").Value = "Pork Last Cooked:"
    Range("L23").Select
    Selection.NumberFormat = "m/d;@"
    
    'If Sheets("PDFPage").Range("M1").Value <= 7 Then ' put n/a for certain days at begin of week
    
    'If Sheets("Period" & P).Range("R" & R - 1).Value = "yes" Then
    Dim rngYes  As Range
    
    With Sheets("Period" & P).Range("R" & R - 9).Resize(10)
        Set rngYes = .Find(What:="yes", _
                           After:=.Cells(1), _
                           LookIn:=xlValues, _
                           LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlPrevious, _
                           MatchCase:=False)
    End With
                           
    If Not rngYes Is Nothing Then
        MyDate = rngYes.Offset(, -16).Value
        Sheets("PDFPage").Range("L23").Value = MyDate
    Else
        Sheets("PDFPage").Range("L23").Value = "N/A"
    End If
End If
Sheets(Current).Select

 

[Joe4]

What sheet is this L23 value on that you are trying to format?
How is L23 being populated?
If you enter this equation into any blank cell on your sheet where the L23 value you are trying to format exists, what does it return?
=ISNUMBER(L23)

 

[andrewb90]

It returns true.

The sheet is "PDFpage"

VBA is going to fill in the value of that cell:

The value is either going to be a time (11:00 AM or 8:00 PM)
or a date 12/30 or 01/06
or text (N/A)

 

[Joe4]

Since you are dealing with multiple sheet, it is best to qualify the sheet along with the range. If you do not, it will default to whatever the current sheet is at that time, i.e.
change this:

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

to this:

    Sheets("PDFpage").Range("L23").NumberFormat = "m/d;@"

 

[andrewb90]

The sheet is always selected beforehand, but i can always specify the sheet to be sure

 

[Joe4]

Note that it is not necessarily the sheet you are on when you kick-off the macro, it is the sheet you are on when it gets to that step of the VBA code. So if the VBA code hops around from sheet-to-sheet, you may be on a different sheet from where you started when it hits that row of code.

Try my suggestion and see if it helps. If it still doesn't work, go to cell L23 on the PDF page, bring up the cell formatting, and see what it shows.

 

[andrewb90]

The formatting is set as date. but if I set it as time, then the date won't work. How would I change it in VBA when I put the value in. (both time & date formatting)

 

[Joe4]

I am not sure I follow.
Changing the formatting of a cell does NOT change the underlying value, only the way it looks.