Ribbon button wanted to apply custom conditional formatting

[Jer3341]

Hi, I'm using Excel 365 on the desktop, version 1908 (employer restricted to the semi-annual channel). I have a custom Ribbon tab and would like to add a button to it that simply by pressing the button will apply conditional formatting to whatever cells are selected. The conditional formatting rule would be as follows:

Rule Type: Format only cells that contain

Rule Description: Format only cells with Cell Value between 990000000 and 999999999

Format: Number as Custom, Type: 0000000000

The purpose of this is that I create numerous spreadsheets every day for work and use them to manipulate data to copy into a non-Microsoft enterprise application. The number sets I work with are nine digits long and start with a '9'. Any numbers that start with a '99' (as opposed to '91', '92', etc.) need to be padded with a leading zero while numbers starting with a single '9' cannot have a leading zero. I want to be able to select a range of numbers, click a button on my custom Ribbon tab, and have Excel add a leading zero to only the numbers beginning with '99'.

Is anyone able to assist with a solution for this?

Thank you

[#2 · Accepted solution]

Rather than a CF rule, you could just apply the cell format [>=990000000]0000000000;# to achieve this. You could make this a Cell Style for easy application.

[#3]

@Savia 

Hi Savia,

Thank you!!! The info on the custom number format is exactly what I needed. I wasn't able to use Cell Styles since Cell Styles are stored per workbook. I was able to add the Cell Style to my personal workbook template, and it works great for all newly-created workbooks, but the data I manipulate is generated from our enterprise application and emailed to me in a spreadsheet, so these spreadsheets do not have the custom Cell Style available and I do not want the extra step of copying the Cell Style from another workbook for each workbook I need to manipulate. However, I easily created a macro in my PERSONAL.XLSB which simply changes the number format to what you gave. I then added the macro to my custom Ribbon tab. Thank you again - this is awesome and will save so much time for me!!!