If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Special formatting
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,D1518,D2029,D32,D3444,D4860,E32:E 44) 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. |
#2
|
|||
|
|||
Special formatting
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 "michael" wrote in message ... 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,D1518,D2029,D32,D3444,D4860,E32:E 44) 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. |
#3
|
|||
|
|||
Special formatting
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. In article , "Max" wrote: Try Conditional Formatting? |
#4
|
|||
|
|||
Special formatting
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. Max wrote: 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 "michael" wrote in message ... 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,D1518,D2029,D32,D3444,D4860,E32:E 44) 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. -- Dave Peterson |
#5
|
|||
|
|||
Special formatting
but since the cell value is coming from a drop-box, it probably
doesn't matter...g In article , Dave Peterson wrote: I think your original formula will evaluate to true no matter how I capitalize HidE. |
#6
|
|||
|
|||
Special formatting
Michael sent me a copy of the sheet and I set it up for him. Perhaps you
have received one too. -- Regards, Tom Ogilvy Dave Peterson wrote in message ... 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. Max wrote: 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 "michael" wrote in message ... 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,D1518,D2029,D32,D3444,D4860,E32:E 44) 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. -- Dave Peterson |
#7
|
|||
|
|||
Special formatting
|
#8
|
|||
|
|||
Special formatting
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 J.E. McGimpsey wrote but since the cell value is coming from a drop-box, it probably doesn't matter...g In article , Dave Peterson wrote: I think your original formula will evaluate to true no matter how I capitalize HidE. |
#9
|
|||
|
|||
Special formatting
perhaps you could kindly drop a couple of lines
here on how the issue was resolved for Michael? thanks Tom Ogilvy wrote in message ... Michael sent me a copy of the sheet and I set it up for him. Perhaps you have received one too. -- Regards, Tom Ogilvy |
#10
|
|||
|
|||
Special formatting
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,D1518,D2029,D32,D3444,D4860 ,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" wrote in message ... perhaps you could kindly drop a couple of lines here on how the issue was resolved for Michael? thanks Tom Ogilvy wrote in message ... Michael sent me a copy of the sheet and I set it up for him. Perhaps you have received one too. -- Regards, Tom Ogilvy |
|
Thread Tools | |
Display Modes | |
|
|