Solved

Excel - Permanent Conditional Formatting

Posted on 2011-03-16
6
741 Views
Last Modified: 2012-05-11
Hi,

Is there a way to save conditional formatting for all Excel 2007 workbooks that are  to be open, without having to redefine the conditions each time?

Thanks
0
Comment
Question by:tahirih
  • 4
6 Comments
 

Author Comment

by:tahirih
ID: 35151277
For example - in Column A

Any cell with:

IS = =yellow
GS = green
SBR = blue
DMG = orange
CLM = purple
CN = red
CR = brown

This will be consistent for values in Column A on many workbooks.
0
 
LVL 33

Accepted Solution

by:
jppinto earned 250 total points
ID: 35151279
You can do it by creating a Personal file (PERSONAL.XLS in Excel 2003 or in Excel 2007-2010 your PERSONAL.XLSB file).

What is it: This is a hidden workbook that opens when you start Excel.
The formatting that you put on this workbook is available in all workbooks you create after in Excel.

Where is it:
If it exists, you can find the file in the Excel startup folder.
Windows XP
C:\Documents and Settings\Ron\Application Data\Microsoft\Excel\XLSTART
In Vista or Windows 7 look here:
C:\Users\Ron\AppData\Roaming\Microsoft\Excel\XLSTART

jppinto
0
 

Author Comment

by:tahirih
ID: 35151287
Not able to locate this file.

Is there a macro I can use to code the information I just offered?

Thank you.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 5

Assisted Solution

by:KPAYNE123
KPAYNE123 earned 250 total points
ID: 35151327

Open a new workbook. Delete all but one of the worksheets by right clicking a tab and selecting Delete.

Change the page setup, if you choose. Go to the File menu in Excel 2003 or earlier and select Page Setup. In Excel 2007, go to the Page Setup group in the Page Layout tab. Adjust margins, change the page orientation and make any other changes you want to apply to default Excel workbooks.

If you want to change the default gridline color in Excel 2007, click the Office Button and select Excel Options. Click Advanced and find Display Options for this Worksheet. Select the Show Gridlines checkbox and choose a color from the Gridline Color drop down menu.

In Excel 2007, click the Office Button. Select Excel Options. Select a font and size under When Creating New Workbooks.

If you want a specific cell in the worksheet to be active when you open a new Excel workbook, click on the cell to select it. When you have made all of your desired changes, save to the file path of the xlstart folder on your computer. (Excel 2007 - C:\Program Files\Microsoft Office\Office 12\XLStart\) Enter Sheet as the file name. In the Save As Type drop down, select Template. Click Save.

Copy the worksheet to other tabs, if you want several tabs in your default workbook. Hold down the Ctrl key, click on the default sheet tab and drag it to the right to copy and create a new worksheet. Go back to the Save As menu and follow the same steps, but this time save it with the name book, again choosing Template in Save As Type. Restart Excel and check out your new default workbook and worksheets.
0
 

Author Comment

by:tahirih
ID: 35151391
KPayne - thank you so much for taking the time in responding. Can you please advise on how to create a macro that is color coded as mentioned above?

Thank you.
0
 

Author Closing Comment

by:tahirih
ID: 35202224
Thank you.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question