Excel stopped formatting

[laqa]

My Excel just stopped formatting cells. Even if I select the column and use the format cells command again it does not work. I tried to select one cell and when I chose "format cells" came a dialog box where I could format the font. I am an university professor and use this command a lot to change dates into numbers to grade students by their task delivery date. Can someone help?

 

[Sufiyan97]

Try below code

Press Alt+F11 and
paste below code

Sub Delete_formats()
    Dim i As Integer
    SendKeys "%c{PgDn}%t{tab}{end}"
    For i = 1 To 100
        SendKeys "%d{end}"
    Next
    Application.Dialogs(xlDialogFormatNumber).Show
End Sub

Click below green button to run the code and see if it appears

 

[laqa]

Try below code

Press Alt+F11 and
paste below code

Sub Delete_formats()
    Dim i As Integer
    SendKeys "%c{PgDn}%t{tab}{end}"
    For i = 1 To 100
        SendKeys "%d{end}"
    Next
    Application.Dialogs(xlDialogFormatNumber).Show
End Sub

Click below green button to run the code and see if it appears

View attachment 39646

 

[laqa]

Thank you very much. I pressed Alt + F11 and nothing happened.

 

[MARK858]

Did Alt F11 not open the VBE so you can paste the code? If it did then you need to follow the 2nd half of the instructions by @A Durfani. If you can't see the green arrow click anywhere in the code and press F5.

 

[jasonb75]

use this command a lot to change dates into numbers to grade students by their task delivery date

If a date will not change to a serial number then that means that the date is not in a valid format to start with.

For example, many dates in dd/mm/yy format will be seen as text strings if your system regional settings are in mm/dd/yy format. This is not the only reason for the problem but it is the most common.

 

[laqa]

Did Alt F11 not open the VBE so you can paste the code? If it did then you need to follow the 2nd half of the instructions by @A Durfani. If you can't see the green arrow click anywhere in the code and press F5.

 

[laqa]

Thanks again, could not find XL2BB. Attaching the image.

 

[Fluff]

XL2BB is an add-in that you would need to download first. It can be found here along with the instructions for use.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[MARK858]

If you are saying that you downloaded XL2BB (not sure what XL2BB has to do with the previous posts btw as it has nothing to do with the VBE), if yes then did you unblock it and did you browse for it in the Addin window?

Did you also see the post by @jasonb75?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

https://www.reddit.com/r/excel/comments/nnsavv

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.