Shading in excel

dcb1

unread,

Jun 27, 2008, 8:28:12 PM6/27/08

to

I created a form in excel. I shaded (filled in cells with color)
areas where I want the user to enter in data. However, I don't want
the shading printed out. Does excel have the capability to do this?

I would appreciate anybody's help,

Thanks

Gord Dibben

unread,

Jun 27, 2008, 8:44:50 PM6/27/08

to

File>Page Setup>Sheet.

Checkmark "Black and White"

You also could use Conditional Formatting to color the cells until users entered
something which would un-color the cells.

Format>Conditional Formatting>Formula is: =cellref=""

Pick a color and OK your way out.

Gord Dibben MS Excel MVP

dcb1

unread,

Jun 27, 2008, 9:51:52 PM6/27/08

to

> >Thanks- Hide quoted text -
>
> - Show quoted text -
First of all, thanks for your help. When I check the black and white
box, it works--- however, there are some headings that I would like to
remain in color (which I didn't say in my first message-- sorry). I
know I should spend a time trying to figure out the conditional
formatting. Nevetheless, I would appreciate if you could walk me
through it so that I can keep some of the headings on the form in
color (on the printout) and still not have the shading show up when
printed. I want several headings to remain in red and a few in blue.

I look forward to your help,

Thanks again

Gord Dibben

unread,

Jun 28, 2008, 12:17:35 AM6/28/08

to

Select a range of cells that users have to enter data in.

Then follow the instructions below to set blank cells to a color.

=cellref="" where cellref is the activecell of your selection.

e.g. assuming headings are in rows 1 and 2 and you want them untouched.

Select A3:C10 then in Formula is: enter =A3=""

Conditional Formatting will apply to all cells in the range.

Those with data will have no color.........those that are blank will be colored
until users fill them in with data.

Gord

dcb1

unread,

Jun 29, 2008, 5:27:21 PM6/29/08

to

> >Thanks again- Hide quoted text -

>
> - Show quoted text -

I still need help. I am unable to figure this out. Again, I created
a form. When I print it, I want several headings (which are in color)
to remain in color when printed along with preventing the areas that
are shaded in different colors (for the user to enter data) from
printing. Could somebody please explain how to use conditonal
formatting and =cellref="" from the help I received. The more you can
break it down for me--- the better. Also, I just want to say thanks
to those that have responded--- of course I have alot to learn about
excel. I am sure I will have more questions.

Gord Dibben

unread,

Jun 29, 2008, 7:59:19 PM6/29/08

to

Please top-post if responding to me.

I will break it down for one cell.

Select A5 which is empty

On the Menu Bar select Format>Conditional Formatting>Condition1.........click on
dropdown arrow and select "Formula is:"

Type in =A5="" that is two double quotes signifying a blank cell.

Hit Format>Pattern and select red from the color picker options.

OK your way out.

A5 will be red until someone enters data, then it will lose the red color.

I can't get more specific than that.

Gord

dcb1

unread,

Jun 30, 2008, 10:58:43 PM6/30/08

to

> >excel. I am sure I will have more questions.- Hide quoted text -
>
> - Show quoted text -I have excel 2007. I went to conditional formatting: new rule, where it tells me to enter a formula: I entered =a5=””, then I went to format; pattern color: selected red and hit ok. The result didn’t show any color. I tried this on my form and a new worksheet. I am sure I am doing something wrong. When you figure this out, can you also tell me what the formula would be for some of the cells where cell has more than 1 column? I have both single and multiply columns for the user to enter data. On the same form and a different problem, I would like to know if there is a way for textwrapping from one row to the next row below.

Every time I hit reply to author, I keep getting "An error was
encounter while trying to post, please try again later." This
happened the last time so I just sent a separate message.

Thank again for your help and patience. I don't kown what you mean by
"top-post".

Gord Dibben

unread,

Jul 1, 2008, 11:55:42 AM7/1/08

to

Having the reply up here means top-post.

Having it below everything as your reply does means bottom-post

I get tired of scrolling down so I prefer top-posting............as do most of
the respondents in these news groups.

Gord

dcb1

unread,

Jul 1, 2008, 4:37:33 PM7/1/08

to

I read your message, but I didn't see any response to the following:
I hope this is showing at the top of the messages. I went to

conditional formatting: new rule, where it tells me to enter a
formula: I entered =a5=””, then I went to format; pattern color:
selected red and hit ok. The result didn’t show any color. I tried
this on my form and a new worksheet. I am sure I am doing something
wrong. When you figure this out, can you also tell me what the
formula would be for some of the cells where cell has more than 1
column? I have both single and multiply columns for the user to enter
data. On the same form and a different problem, I would like to know
if there is a way for textwrapping from one row to the next row below.

Thanks

> >"top-post".- Hide quoted text -

Gord Dibben

unread,

Jul 1, 2008, 5:12:43 PM7/1/08

to

Is A5 completely blank?

No spaces or formulas that make it look blank?

Should work for single cells and for merged cells.

i.e. merge A5:D5 and use =A5="" in the CF>Formula is:

You can wrap text only in a single cell not a cell below.

Note that merged cells will wrap text, but Row>Autofit won't work.

If you want to upload a copy of your workbook to one of the file hosting sites
we can download to see what could be preventing CF from working.

http://www.freefilehosting.net/
http://savefile.com/

When you have uploaded to that site, post the URL so we could download should we
choose.

BTW........ the term "new rule" may be a 2007 version feature.

I don't use 2007 so might be steering you wrong on this. I know CF has changed
with 2007 version.

Gord

dcb1

unread,

Jul 2, 2008, 9:53:25 PM7/2/08

to

Thanks, I was able to get the shading to disappear when data is
entered. Here is a another problem. Again, in Excel 2007,
under....... Developer tab to Insert Controls to Form Controls; I
inserted a checkbox. The checkbox borders are in black. How do I
change them to another color? Also, I know you said there was not a
way to textwrap from a single cell the cell directly below it... any
other suggest besides opening up a textbox?

Thanks again for your help and let me know if my message is 'top-
posted.'

Also, why do I get an error when attempting to Reply to author?

On Jul 1, 4:12 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> Is A5 completely blank?
>
> No spaces or formulas that make it look blank?
>
> Should work for single cells and for merged cells.
>
> i.e. merge A5:D5 and use =A5="" in the CF>Formula is:
>
> You can wrap text only in a single cell not a cell below.
>
> Note that merged cells will wrap text, but Row>Autofit won't work.
>
> If you want to upload a copy of your workbook to one of the file hosting sites
> we can download to see what could be preventing CF from working.
>

> http://www.freefilehosting.net/http://savefile.com/

> >> - Show quoted text -- Hide quoted text -

Gord Dibben

unread,

Jul 3, 2008, 11:10:44 AM7/3/08

to

In Excel 2003 the checkbox has no border but you can change the BackStyle to
Transparent.

Don't know about 2007.

You will get an error when replying to author because I don't post my true email
address.

Best to keep things within the news group unless someone specifically requests
you go off the board.

Yes.......you are top-posting

Gord