Hiding cells based on the value in another cell

[Paul Kraemer]

Hi,

I am using Excel 2007. I have created a worksheet where I would like to be
able to "hide" a group of cells based on the value of another cell.

I can do what I want by inserting a shape (rectangle) and giving it a white
background and a white line color. As I have chosen not to show gridlines on
my worksheet, this rectangle effectively hides the cells that are behind it.

I can write code to control the visibility of this rectangle based on the
value in a particular cell. I imagine that there is a way I can get this
code to run anytime the value in this cell changes. I am pretty sure that I
can do what I want.....but...

I just wanted ask if there is a better way to do this sort of thing? If
anyone has any recommendations, I'd really appreciate it.

Thanks in advance,
Paul

 

[Ron@Buy]

From your description 'Conditional Formating' would seem to be a simple
solution.
i.e. when the condition in your cell meets the criteria automatically change
font colour to match that of the cell background (usually white) thus hiding
the content.
Come back with precise detail if you require further help.

 

[Paul Kraemer]

Hi Ron,

That sounds like a good idea, but my only problem is that in the cells I am
trying to hide, I used several different fill colors and several different
border styles. With conditional formatting, I think I would have to deal
with alot of differnent cells individually instead of being able to apply one
conditional format to all of them. I think my idea of hiding/unhiding a
white rectangle to cover/uncover the cells will be easier for me in this
case. It is good to have another option though in case I get stuck.

Thank you for your help,
Paul

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$2" And Target.Value = "qwerty" Then

Shapes("Rectangle 1").Fill.Transparency = 1#
Else
Shapes("Rectangle 1").Fill.Transparency = 0#

End If
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP