macro to hide data (with conditional formatting?)

G

Ginna Moore

I have a large spreadsheet and I want it to automatically HIDE a row if a
certain cell value meets a specific criteria. Does anyone know if this can be
done? I know I can do this manually but would like to automate if possible.

Thanks.
 
S

Sandy Mann

You can hide the values in the row with Conditional Formatting but the
values will still be there. You can hide a Row when a next value is entered
into it and a cell in the row reaches a predetermined value. However, doing
just that would be confusing and the cursor would still be in the same, (now
hidden) row and other unseen data could be added.

It would be better if you described what it is that you want to do but this
Worksheet module code will hide the row that the cursor is in when Column J
in that row is greater than 10. It gives a message box warning then hides
the row and places the cursor in the next visible row down in Column A

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim x As Long
If Cells(Target.Row, 10).Value > 10 Then
MsgBox "You have reached the Target value" & vbLf & _
"This Row will now be hidden"

Target.EntireRow.Hidden = True

x = Target.Row
Do Until Cells(x, 1).EntireRow.Hidden = False
x = x + 1
Loop

Cells(x, 1).Select

End If
End Sub

Post back saying what it is that you want to do.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top