Excel stopped formatting

laqa

New Member
Joined
May 28, 2021
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Apr 12, 2019
Messages
408
Office Version
  1. 2013
Platform
  1. Windows
Try below code

Press Alt+F11 and
paste below code

VBA 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

1622272538865.png
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

laqa

New Member
Joined
May 28, 2021
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
Try below code

Press Alt+F11 and
paste below code

VBA 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

New Member
Joined
May 28, 2021
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
Thank you very much. I pressed Alt + F11 and nothing happened.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,197
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,607
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
May 28, 2021
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
May 28, 2021
Messages
12
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Attachments

  • Searching for XL2BB.png
    Searching for XL2BB.png
    60 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,668
Office Version
  1. 365
Platform
  1. Windows
XL2BB is an add-in that you would need to download first. It can be found here along with the instructions for use.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,197
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,668
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,863
Messages
5,709,074
Members
421,613
Latest member
wyzco

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
Top