Excel: conditionally hide range of cells

[raybo]

How would I go about writing a formula or macro that would conditionally hide the values in a range of cells based on the value in one of those cells?

Ex. If S1="m" then Hide Range(a1:IV14)

I'm sure there is a way to write a macro that would look at S1 and if the value of that cell = "m" then it could change the font color, in the range (A1:IV14), to the same color as the background, thus making the range invisible. Or something along those lines. I've searched the help files for Excel and Visual Basic but find no mention of a "Hide" function that is conditional.

Doing a conditional format on the whole range is a possibility but to conditionally format all the cells in range(A1:IV14) would take a long time. I doubt that copying the conditional format from S1 to all the rest of the cells would work, although I haven't tried that yet. Seems to me that when you do that the cell reference (S1) changes depending on the location of the other cells.

[facorsig]

I used to use this conditional formatting function a lot to change the background color of cells to green for profitable and red for unprofitable. An example was the cell entry, =IF(J3>AB3,"GREEN","NOT GREEN"). I would use a second function through Format, Conditional Formatting to reset the font or background color.

Fred

[raybo]

I used to use this conditional formatting function a lot to change the background color of cells to green for profitable and red for unprofitable. An example was the cell entry, =IF(J3>AB3,"GREEN","NOT GREEN"). I would use a second function through Format, Conditional Formatting to reset the font or background color.

Fred

Yeah, I've used conditional formatting to highlight all kinds of things, especially spot play qualification, current form indicators, and the like. However, I need to format an entire block of cells with an "IF statement" and don't know how to get the conditional to apply to the entire block without doing it on each individual cell. Very time consuming to do it that way.

[robert99]

This is what the visual basic and macros are provided for.
You can attach code to a button and one click does the whole range for you.

[raybo]

This is what the visual basic and macros are provided for.
You can attach code to a button and one click does the whole range for you.

I am very familiar with recording macros, however, I'm not very skilled at writing them. I need an "IF" statement that would look at cell "S1" and if it equals "m" then hide cells A1:IV14. If you can write this statement I would be eternally grateful!

[hcap]

I used a formula in the "conditional format" dialogue box itself.
Formula works by allowing the true condition to set the format

I set "format" to have font color set to white.


=IF($S$1="m",1,0)

Right now there is a "m" in cell S1.
Type anything else and data range will "unhide"

[hcap]

The conditional formatting dialogue box looks like this when setting the formula

[raybo]

I used a formula in the "conditional format" dialogue box itself.
Formula works by allowing the true condition to set the format

I set "format" to have font color set to white.


=IF($S$1="m",1,0)

Right now there is a "m" in cell S1.
Type anything else and data range will "unhide"

hcap,

Thanks for the formula. I knew there had to be a way to do it. I hadn't thought of selecting the range(A1:IV14) and then doing a conditional format on the entire range at once. I was envisioning having to conditionally format each and every cell in the range. DUH!!!!!!!

It works like a champ!!

[hcap]

Ray,

The format itself may be copied to any other range.
I actually set the format for one column first. Made sure it worked then copied it to all the other columns in the range you requested. The dollar signs in the formula keep everything constant always referring to cell S1----$S$1

[raybo]

Ray,

The format itself may be copied to any other range.
I actually set the format for one column first. Made sure it worked then copied it to all the other columns in the range you requested. The dollar signs in the formula keep everything constant always referring to cell S1----$S$1

Yeah, I see that. I was thinking that the cell reference changed like it does when copying regular formulas. Thanks again!