SOLVED

Ribbon button wanted to apply custom conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-1172045%22%20slang%3D%22en-US%22%3ERibbon%20button%20wanted%20to%20apply%20custom%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1172045%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I'm%20using%20Excel%20365%20on%20the%20desktop%2C%20version%201908%20(employer%20restricted%20to%20the%20semi-annual%20channel).%20I%20have%20a%20custom%20Ribbon%20tab%20and%20would%20like%20to%20add%20a%20button%20to%20it%20that%20simply%20by%20pressing%20the%20button%20will%20apply%20conditional%20formatting%20to%20whatever%20cells%20are%20selected.%20The%20conditional%20formatting%20rule%20would%20be%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERule%20Type%3A%20Format%20only%20cells%20that%20contain%3C%2FP%3E%3CP%3ERule%20Description%3A%20Format%20only%20cells%20with%20%3CFONT%20color%3D%22%230000FF%22%3ECell%20Value%3C%2FFONT%3E%20between%20%3CFONT%20color%3D%22%230000FF%22%3E990000000%3C%2FFONT%3E%20and%20%3CFONT%20color%3D%22%230000FF%22%3E999999999%3C%2FFONT%3E%3C%2FP%3E%3CP%3EFormat%3A%20%3CFONT%20color%3D%22%230000FF%22%3ENumber%3C%2FFONT%3E%20as%20%3CFONT%20color%3D%22%230000FF%22%3ECustom%3C%2FFONT%3E%2C%20Type%3A%20%3CFONT%20color%3D%22%230000FF%22%3E0000000000%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20purpose%20of%20this%20is%20that%20I%20create%20numerous%20spreadsheets%20every%20day%20for%20work%20and%20use%20them%20to%20manipulate%20data%20to%20copy%20into%20a%20non-Microsoft%20enterprise%20application.%20The%20number%20sets%20I%20work%20with%20are%20nine%20digits%20long%20and%20start%20with%20a%20'9'.%20Any%20numbers%20that%20start%20with%20a%20'99'%20(as%20opposed%20to%20'91'%2C%20'92'%2C%20etc.)%20need%20to%20be%20padded%20with%20a%20leading%20zero%20while%20numbers%20starting%20with%20a%20single%20'9'%20cannot%20have%20a%20leading%20zero.%20I%20want%20to%20be%20able%20to%20select%20a%20range%20of%20numbers%2C%20click%20a%20button%20on%20my%20custom%20Ribbon%20tab%2C%20and%20have%20Excel%20add%20a%20leading%20zero%20to%20only%20the%20numbers%20beginning%20with%20'99'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20anyone%20able%20to%20assist%20with%20a%20solution%20for%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1172045%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1172067%22%20slang%3D%22en-US%22%3ERe%3A%20Ribbon%20button%20wanted%20to%20apply%20custom%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1172067%22%20slang%3D%22en-US%22%3ERather%20than%20a%20CF%20rule%2C%20you%20could%20just%20apply%20the%20cell%20format%20%5B%26gt%3B%3D990000000%5D0000000000%3B%23%20to%20achieve%20this.%20You%20could%20make%20this%20a%20Cell%20Style%20for%20easy%20application.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1172346%22%20slang%3D%22en-US%22%3ERe%3A%20Ribbon%20button%20wanted%20to%20apply%20custom%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1172346%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Savia%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!!!%20The%20info%20on%20the%20custom%20number%20format%20is%20exactly%20what%20I%20needed.%20I%20wasn't%20able%20to%20use%20Cell%20Styles%20since%20Cell%20Styles%20are%20stored%20per%20workbook.%20I%20was%20able%20to%20add%20the%20Cell%20Style%20to%20my%20personal%20workbook%20template%2C%20and%20it%20works%20great%20for%20all%20newly-created%20workbooks%2C%20but%20the%20data%20I%20manipulate%20is%20generated%20from%20our%20enterprise%20application%20and%20emailed%20to%20me%20in%20a%20spreadsheet%2C%20so%20these%20spreadsheets%20do%20not%20have%20the%20custom%20Cell%20Style%20available%20and%20I%20do%20not%20want%20the%20extra%20step%20of%20copying%20the%20Cell%20Style%20from%20another%20workbook%20for%20each%20workbook%20I%20need%20to%20manipulate.%20However%2C%20I%20easily%20created%20a%20macro%20in%20my%20PERSONAL.XLSB%20which%20simply%20changes%20the%20number%20format%20to%20what%20you%20gave.%20I%20then%20added%20the%20macro%20to%20my%20custom%20Ribbon%20tab.%20Thank%20you%20again%20-%20this%20is%20awesome%20and%20will%20save%20so%20much%20time%20for%20me!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 Replies
Highlighted
Best Response confirmed by Jer3341 (New Contributor)
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.
Highlighted

@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!!!