MrExcel Publishing
Your One Stop for Excel Tips & Solutions

conditional formatting--need for visual basic?

Posted by Coline on January 18, 2002 5:10 PM

Hi, I have a worksheet in which I would like to do the following three things. Is visual basic necessary?
1. Repeat tables (including formulas & existing inputs from the previous table) for x times, based on the # of years of program operation (e.g., if a program were open for 5 years, there would be 5 tables. Table 2 = all inputs from table 1 + any changes, table 3 = all inputs from table 2 + any changes, etc.)
2. Maintain formulas used for calculations within each table.
3. Distinguish cells with formulas from those cells in which data have been entered manually by shading, etc.

I've accomplished the first two, but since I'm repeating tables, all cells currently have formulas in them. Any suggestions on how to accomplish this task with elegance, perhaps without learning VB?

Posted by Jack in the UK on January 21, 2002 12:43 PM

Hi Coline--
TYhis is a nasty quick fix but cant see why not to use

one any sheel or select all sheets
press CTRL+G
select special
tick formula
the box disapears and all the formulas are selected
now shade as normal from fill button.

Nasty crude but hey its works and takes 5 seconds

Good luck
Jack in the UK

Though if you need a lot more post email address ill contact you to discuss.

Posted by Coline on January 23, 2002 3:26 PM

Thanks so much, Jack! Nice fix!