Special formatting

  • Thread starter Thread starter michael
  • Start date Start date
M

michael

I have tried to get an answer to this 3 times, but still
have not been able to get this done in the way I am
looking to.

I have a sheet that I use for calculating the closing
costs of purchasing a house.

I have a drop-box that I want to use as a switch. In cell
D8 The drop box that has Show,Hide is what I want to use
as the switch.

I want to make it so that if D8 says "Hide" then the
following characters in the following cell ranges become
White (and therefore not visible)

Change the text in this range to White
(C50:C52,D15:D18,D20:D29,D32,D34:D44,D48:D60,E32:E44)

I also want to have the box formatting removed from the
following cell range:
(C21,C50:C52,C12:C53)

If the box in D8 says show, the cell text in the above
range would be black and the boxes would show in the
other range. How can I do this with an existing drop box
and and in a simple manner.

Help greatly appreciated.
 
Try Conditional Formatting?

ok, the drop box for "show" / "hide" is in D8

Text Formatting
--------------------
Select say C50:C52

Click Format > Conditional Formatting

Settings:

Condition 1
Formula Is | =$D$8=UPPER("hide")
Click Format > Font tab > select white for Color > OK

Click Add >>

Condition 2
Formula Is | =$D$8=UPPER("show")
Click Format > Font tab > select Automatic for Color > OK

Click OK

Double-click on the Format Painter icon ("brush")

Just select & "paint" over all the other cells to be similarly formatted
with the mouse

Press Esc key when done to revert cursor to normal

Border Formatting
----------------------
In a similar manner like above

Select C21

Click Format > Conditional Formatting

Settings:

Condition 1
Formula Is | =$D$8=UPPER("hide")
Click Format > Border tab > click on None (under Presets) > OK

Click Add >>

Condition 2
Formula Is | =$D$8=UPPER("show")
Click Format > Border tab > select Outline (under Presets) > OK

Click OK

Double-click on the Format Painter icon ("brush")

Select & "paint" over all the other cells to be similarly formatted
with the mouse

Press Esc key when done to revert cursor to normal
 
Note that if the dropdown cell can only have Show or Hide, this
solution can be shortened a bit by formatting the cells the way you
want when the value is Show, then using only the one condition to
hide the text and borders if the value is Hide.
 
And instead of using:

=$D$8=UPPER("hide")
You may want to look at
=exact($d$8,upper("hide"))
or
=exact($d$8,"HIDE")

I think your original formula will evaluate to true no matter how I capitalize
HidE.
 
but since the cell value is coming from a drop-box, it probably
doesn't matter...<g>
 
Michael sent me a copy of the sheet and I set it up for him. Perhaps you
have received one too.
 
well, precisely the point <g>, since the OP
did state the use of the "switch" in D8 in his post

but your fine points/tweaks given
are noted with thanks, Dave & JE!

cheers
 
perhaps you could kindly drop a couple of lines
here on how the issue was resolved for Michael?

thanks
 
It really was no different from what you suggested although simpler.

Using conditional formatting

=$D$8="Hide"

As JE said, the selection was made using a drop down

but even if typed in, any form of hide should be acceptable and this does
accept any form.

Some of the formatting overlapped, so some cells required multiple
formatting, plus some of the cited cells did not appear to be correct.

Basically I put the spec in the immediate window (copied from the
email/posting)

Range("C50:C52,D15:D18,D20:D29,D32,D34:D44,D48:D60,E32:E44").Select

and then applied the formatting (for the heck of it I did a second format
for Show, but it wasn't necessary).

Did the other set, cleaned up the overlap. Done.

Apparently Michael wasn't following your instructions or he sent it to me
before he received your instructions.

--
Regards,
Tom Ogilvy






Max said:
perhaps you could kindly drop a couple of lines
here on how the issue was resolved for Michael?

thanks
Click to expand...
 
Thanks, Tom! cheers.

Tom Ogilvy said:
It really was no different from what you suggested although simpler.

Using conditional formatting

=$D$8="Hide"

As JE said, the selection was made using a drop down

but even if typed in, any form of hide should be acceptable and this does
accept any form.

Some of the formatting overlapped, so some cells required multiple
formatting, plus some of the cited cells did not appear to be correct.

Basically I put the spec in the immediate window (copied from the
email/posting)

Range("C50:C52,D15:D18,D20:D29,D32,D34:D44,D48:D60,E32:E44").Select

and then applied the formatting (for the heck of it I did a second format
for Show, but it wasn't necessary).

Did the other set, cleaned up the overlap. Done.

Apparently Michael wasn't following your instructions or he sent it to me
before he received your instructions.

--
Regards,
Tom Ogilvy
Click to expand...
 
Back
Top