VBA .Find Not Working with Formula

[thejg89]

Hello,

I'm working on a dynamic valuation model with 80+ worksheets. The 'Settings' worksheet has numerous check boxes to determine which sheets need to be visible or hidden. Within in sheet, there are rows that only need to be shown if the corresponding account detail 'sheet' is visible and a column that will alert the user if they need to 'HIDE' the row.

For example: lets assume on a Balance Sheet it lists 'vehicles and cars' as a row but if the 'vehicles and cars' detail sheet is hidden there would be an indicator on the Balance Sheet to 'HIDE' this row as it is not used in the analysis. Hopefully that helps explain the functionality.

I have the code working for 'hardcoded' cell that = "HIDE" but it doesnt work if 'HIDE' is the result of an IF Statement (continuing the example ... one the column that indicates if a row needs to be hidden would have an equation something like =if( settings sheet checkbox for 'cars/vehicles' = checked, 'blank', 'HIDE').

I've included an excel sheet, over simplified, but it should show the general understanding. The code was under a module but i moved it to ThisWorksheet. Below is also a copy of the code with comments as to what I was trying to accomplish

This is my first post - so THANK YOU!!!! everyone who tries to help

Sub toggleHide()

    Dim Sh As Worksheet, rnCheck As Range, rnFind As Range, stAddress As String
    
    Application.ScreenUpdating = False ' dont update screen till end
    
    For Each Sh In ThisWorkbook.Worksheets ' numerous sheets either active / in active based off settings
    
        If Sh.Visible = xlSheetVisible Then ' only run for visible sheets to keep macro fast
        
            Sh.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 'collapse all groups bc they dont need to be seen
            
            Set rnCheck = Sh.UsedRange.SpecialCells(xlCellTypeConstants) 'dynamically find the usedRange as it changes sheet to sheet
            
            With rnCheck
                Set rnFind = .Find(What:="HIDE") 'THIS IS THE PROBLEM - it will work if text in the cell = 'HIDE' but not if its a result of an if statment
                
                If Not rnFind Is Nothing Then
                    stAddress = rnFind.Address
            Do
                rnFind.EntireRow.Hidden = Not Range("Status").Value
                Set rnFind = .FindNext(rnFind)
            Loop While Not rnFind Is Nothing And rnFind.Address <> stAddress
            
                End If
        
             End With
        
        End If
        
    Next Sh

    Application.ScreenUpdating = True
    
    Range("Status") = Not Range("Status") 'toggles hide / unhide rows in case a user wants to see all rows
    
End Sub