automatically hide/unhide the row based another sheet cell value.

[sumukhreddy]

Hello,

I'm new to the excel VBA programming & now I've one problem:banghead:. Recently I've created one tracker which has multiple sheets. I've linked the sheet 2 E column with sheet 1 O column, so that whatever the value in O column cells will be updated automatically in sheet 2 E column cells. I've created the drop down list with the options like Yes, No, Back up & On hold. I need a macro in sheet 2 hide the rows where the E column cells does not equal to Yes. I've the below code but it is working only if I type the options manually:crying:. I need the macro do it automatically like the moment my teammate choose the option other than yes in sheet 1 O column, then the respective rows in sheet 2 must be hide.

It is fine for me, if you write the code that can be directly work based on the sheet 1 O column values.:clap:

Can anyone help me to resolve this issue?

Sub hide_row()
Application.ScreenUpdating = False
Dim C As Range
For Each C In Range("E3:E101").Cells
If C.Value <> "Yes" Then
C.EntireRow.Hidden = True


Else
C.EntireRow.Hidden = False


End If
Next C
Application.ScreenUpdating = True


End Sub


Thanks in advance.

[Logit]

Right click on the Sheet 2 tab and select VIEW CODE. In the right side window paste this macro :



Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim C As Range
For Each C In Range("E3:E101").Cells
If C.Value <> "Yes" Then
C.EntireRow.Hidden = True


Else
C.EntireRow.Hidden = False


End If
Next C
Application.ScreenUpdating = True
End Sub

[sumukhreddy]

22102Thank you Logit for your support, but unfortunately this code is not working as per my requirement. For better understanding of my problem I'm uploading the sample document for your reference.

[Logit]

.
The macro works, when you type anything - anywhere in the sheet.

An alternative would be to use a Command Button and place the macro into a Routine Module. Then when you want to hide
all the non-Yes rows, you click the button. See attached file.



Option Explicit


Sub HideNotYes()


Application.ScreenUpdating = False
Dim C As Range


For Each C In Range("E3:E101").Cells
If C.Value <> "Yes" Then
C.EntireRow.Hidden = True
Else
C.EntireRow.Hidden = False
End If
Next C

Application.ScreenUpdating = True


End Sub

[sumukhreddy]

Thank you for your support.