hide/show range of cells by condition

[eugz]

Hi All.
I'm newer in VBA. I will appreciate for help creating vba procedure. I would like to hide/show range of cells by that condition:

Please Login or Register  to view this content.

What I mean. If value in cell M2 changed then according range will hide or show. If it posible how to do it?
Thanks.

[Palmetto]

I would use the Select Case method - the code is a bit cleaner looking.
Per your example, you are hiding rows 20:27 in each test of the value in cell M2. Change column references has no effect when hiding rows.

If you want hiding of the rows to occur when M2 changes, then you must use the Worksheet_Change event.

Please Login or Register  to view this content.

[eugz]

Thanks for replay.
I copy and past you code in to worksheet1 in VBA. But when I chande value in cell B2 the range cells by condition not hide. I also modify your code like this

Please Login or Register  to view this content.

Because I'm expecting to hide exectly range of cells not range of whole rows. Where is problem?
Thanks.

[Palmetto]

Because I'm expecting to hide exectly range of cells not range of whole rows. Where is problem?

You cannot hide a range of cells. You must hide either the entire row or the entire column in which the cell resides.

[eugz]

Thanks for help.
Other idea. I cannot hide cells range but I can format cells range by ";;;" (3 semicolons ) to hide cells value. My question is how to change cells range format by condition? I mean if for example B2 = Jan so cells range L20:L27 formated to text, date, number, and so on, but if B2 = Apr so cells range L20:L27 will change format to ";;;". If that idea will work how to do it.
Thanks.

[Palmetto]

Your profile doesn't specify which version of Excel you are using (please complete this part of your profile).

You can use Conditional Formatting

In Excel 2000 - 2003, you are limited to a maximum of three conditional formats and will need to use VBA. See this link: Get Around Excels 3 Criteria Limit in Conditional Formatting

If you have Excel 2007 you are not limited in the number of Conditional Formats and not need VBA.

[eugz]

Thanks more time.
I tried to play with Format Condition it is good if no more then 3 conditions.
In my case I have 3 groups conditions. How using Format Condition group?
Thanks.

[Palmetto]

In my case I have 3 groups conditions. How using Format Condition group?

See the link I provided in my previous post. You will have to use VBA if you have more than three conditions.

[eugz]

Hi. Thanks for the help.
I just show my new code and will try to explaine my problem.
In my spreadsheet user enter first date of the month only to cell B3. The rest other date value of month will get by formula =IF(B3="","",B3+n) where n - day of month. Because that file will use for some years I formated cell M2 like date MMM so for any year that cell will display only three letters of month by formula M2 like =IF(B3="","",B3). The dates located on ranges B3:K3, B12:K12, and B21:L21. And I would like when M2 has values "Apr", "Jun", "Sep", "Nov" font color of Range("L21:L28") change to white. If M2 has value "Feb" font color of Range("J21:L28") change to white. And if M2 has values "Jan", "Mar", "May", "Jul", "Aug", "Oct", "Dec" font color of Range("L21:L28") will change to black. That is my code:

Please Login or Register  to view this content.

When I run the code and enter, for instance, to cell B3 value "4/1/2009" ofcause M2 change own value to "Apr" but color of font in Range("L21:L28") doesn't changed. How to fix that problem?
Thanks.

[shg]

Please Login or Register  to view this content.

In versions of Excel prior to 2007, assigning an RGB color to a font or a cell will result in Excel choosing the closest color in the 56-color palette.

[eugz]

Thanks for replay.
When I used that code

Please Login or Register  to view this content.

the color for range L31:L35 changed to red. But for me need to chande color if value in the cell chahge dynamically by condition. If user will enter according date value like I discribe in previous post the font for according range is changing.
Thanks.

[eugz]

Hi All.
Is it possible to change font color of range by condition based on a existing date or date will entered by user. I mean, if user will change or enter date value in cell B2, is it possible to change font color depends on date value in range L20:L27?
Thanks.