Solved: Hiding Cells

[gimli]

Hey all,

I read about hiding rows and columns here. Not sure if its possible but Id like to hide cells without hiding the whole row or column. Problem is if I hide the rows or columns it will hide other data that I want shown. I want them hidden based on another cells value...lets say

EX. E10 > 10 hide cells E21:H24

Im thing a sub activated by cell E10


any ideas would be great.

[mbarron]

You cannot hide individual cells or ranges. What would take their place? You could hide the results by making the font color match the background color.

[gimli]

Ok..makes sense..guess ill look around for vba to change font color and backround color if it exists

[mbarron]

You can use this change event. It will change the font color to match the background color for the E21:H24 range if the value in E10 is greater than 10. If it less than 10, the font color will be black. It will not work if the color for the backgrounds are caused by conditional formatting. Then again, you could use conditional formatting to do the color change (:omg2::smacks head after writing code to do a built in feature)

The one advantage is if you've set multiple colors (manually) , the code will match those colors without having to edit the CF.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
If Not Intersect(Target, Range("E10")) Is Nothing Then
If Target > 10 Then
For Each rCell In Range("E21:H24")
With rCell
.Font.Color = .Interior.Color
End With
Next
Else
For Each rCell In Range("E21:H24")
With rCell
.Font.ColorIndex = 0
End With
Next
End If
End If
End Sub

[gimli]

hey baron,

thanks much!