Colorcoding cells

[c991257]

Hi, is there a way to colorcode cells in a sheet to easily identify them and at the same time not print the cell as colored?

I have a few sheets I update on a monthly basis and usually I enter text into one or two cells cell and formulas then give me the data I need. Sometimes I share the files with some collegues, and it would make it easier for them to read if the input cells had some kind of color coding.

I know how to do using macros but this should be relatively simple and Excel probably can do it already.

/Møller

[Carim]

Hi,

You can have a working convention ...
say all blue cells are input cells ...

Carim

[c991257]

Sure, but I don't wan't the blue fill to be printed. The choice of color is not the problem.

[oldchippy]

Sure, but I don't wan't the blue fill to be printed. The choice of color is not the problem.

Go to Page Set up > Sheet, tick print Black & White

[Carim]

Ok I understand ...
you could have a tiny macro which turns on and off the colorcoding to allow for printing in black ...

HTH
Carim

[c991257]

Go to Page Set up > Sheet, tick print Black & White

Won't work because I also have graphs on the page.

[Carim]

Well ...

your macro would only handle worksheet cells ...
so nigun problemo ...

Carim

[c991257]

Well ...

your macro would only handle worksheet cells ...
so nigun problemo ...

Carim

Yeah that simple enough. Instead of having two buttons in the sheet, is there a way to use a tick box? I can't see how I make it run two macros depending on tick/no tick.

/Møller

[Carim]

Yes, you are right you can have options buttons Yes - No type ...
with a linked cell which will show as a value either 1 or 2 ...

Then, in your macro, you only have to test this specific range for its value ...

HTH
Carim

[Maistrye]

Yeah that simple enough. Instead of having two buttons in the sheet, is there a way to use a tick box? I can't see how I make it run two macros depending on tick/no tick.

/Møller

Here's an example for you that I use for one of my sheets:

Please Login or Register  to view this content.

Basically, I have a check box (set to not print) on the page, and this subroutine is attached to it as the macro to run.

The check box has a cell (which I've called "GG_Checkbox_Value" because I lack imagination) where the value is stored (ie. either true or false), and my subroutine checks the value of this cell to determine what needs to be done.

Scott

[c991257]

But shouldn't you be able to avoid the TRUE/FALSE cell if you use a tick from the control toolbox instead of the one from forms?

/Møller

[Maistrye]

But shouldn't you be able to avoid the TRUE/FALSE cell if you use a tick from the control toolbox instead of the on from forms?

/Møller

The code still works, you just reference the your checkbox instead of the cell.

Scott

[Mallycat]

Why don't you just write a macro that turns off the colours before it prints.

Turn on macro recording and re-set all the colours. This will be your "turn colours back on" macro. call it

Please Login or Register  to view this content.

Then put this macro in the Workbook module

Please Login or Register  to view this content.

Only problem is it wont turn the colours back on automatically after a print (ther is no AfterPrint event). You could change the approach slightly, and put a "print without colour button" on the sheet that first turns off colours, prints the sheet, then turns back on colours.

Matt