Hide column and rows based on values

[StorminASU]

Hello everyone, I have a workbook that needs to hide columns if a cell value is less than 0 and, as something entirely separate, hide numerous rows if nothing is displayed in them. Both of these will be pulling their conditional cells from cells with formulas in them. For example, the macro which will decide to hide rows will be targeting cells that have formulas which are set to "" if they need to be hidden. Same thing with the column.

Currently I'm using this code, but it doesn't seem to be updating as the formula in W10 changes. Here is the formula in W10 now: "=IF(W18>0,"TRUE","FALSE")"

Here is the code to hide the column, which is working, but not as the formula changes:

Private Sub Worksheet_Change(ByVal Target As Range)    If Not Application.Intersect(Target, Me.Range("W10")) Is Nothing Then
        If Target.Cells(1).Value = "TRUE" Then
            Me.Columns("w").Hidden = False
        Else
            Me.Columns("w").Hidden = True
        End If
    End If
    
End Sub

In addition to not knowing how to get the column to hide/unhide more automatically based on the formula results, I have no clue how to hide/unhide the rows. For values in B69:B100, if the formula result is "", the row needs to be hidden. The formula in column B is as follows, with a blank row between each 5 year increment.
=IF(B68="","",IF(B68=0,0,IF(B68+1>100,"",B68+1)))


Thank you very much for any help you can offer!

 

[Yongle]

Instead of using TEXT for "True" , "False" use Excel's inbuilt True & False values

A simpler formula in W10 (returns True if condition met and False if not)
=W18>0

And amend code accordingly
This

If Target.Cells(1).Value = "TRUE" Then

would change to

If Target.Cells(1).Value = [COLOR=#ff0000]True[/COLOR] Then

Identifying correct trigger
Your code cannot work because you are usng the wrong TRIGGER
- change arising when the formula returns a different value is NOT a trigger

W10 is changed by formula - cannot use W10 as trigger
W10 changes when W18 changes
W18 is changed by formula -cannot use W18 as trigger
What is causing the formula in W18 to change?

Need to identify the cell (or range) which is amended which results in formula in W18 returning a different value

 

[StorminASU]

Identifying correct trigger
Your code cannot work because you are usng the wrong TRIGGER
- change arising when the formula returns a different value is NOT a trigger

W10 is changed by formula - cannot use W10 as trigger
W10 changes when W18 changes
W18 is changed by formula -cannot use W18 as trigger
What is causing the formula in W18 to change?

Need to identify the cell (or range) which is amended which results in formula in W18 returning a different value

Thank you Yongle. W18 is pulling from a second worksheet, which is pulling an IRR formula calculation. If the calculation is >0, then the column needs to be displayed. If the IRR formula is <0, the column needs to remain hidden. The IRR formula is being updated per data that is being entered pertaining to each client's needs: timing of loan, rate, methods of payment, etc.

The rows are (perhaps) simpler: the worksheet model for each client goes from current age to age 100. After age 100, the "Age" column is set to return "". If the value for the Age column is "", that row needs to be hidden.

If there is a way to upload the document, I certainly can. Thank you so much!

 

[Yongle]

W18 is pulling from a second worksheet, which is pulling an IRR formula calculation. If the calculation is >0, then the column needs to be displayed. If the IRR formula is <0, the column needs to remain hidden. The IRR formula is being updated per data that is being entered pertaining to each client's needs: timing of loan, rate, methods of payment, etc.

Therefore the cells used for data entry is the trigger range
You could be lazy and simply test the value whenever any change occurs by removing line

If Application.Intersect ....
( and its partner ... End If)

 

[Yongle]

Presumably the client's DOB is a static value.
The rows remain hidden forever once hidden unless an error was made.
The DOB cell would be the obvious trigger

 

[Yongle]

Before you do anything further ....

There is a way to tackle this at workbook level - rather than sheet level

is there a separate sheet for every individual client ?

 

[StorminASU]

Before you do anything further ....

There is a way to tackle this at workbook level - rather than sheet level

is there a separate sheet for every individual client ?

Each Client has a separate workbook: there is a data entry tab (rates, client info, etc.), (2) sheets calculating 2 different IRRs showing cumulative cash flow, and a summary/presentation tab that is used to print off and discuss with the client.

 

[Yongle]

that is fine - keep going as you are

But, for info,
- Workbook_SheetChange (which goes in ThisWorkbook module) applies to all sheets (or specific sheets if required) and can avoid the need for identical Worksheet_Change being replicated for multiple sheets
- your sheets are different to each other and so this is irrelevant

However
- if all your client are identical, there may be is a case for a central repository for some of the code, instead of replicating across multiple workbooks

 

[StorminASU]

that is fine - keep going as you are

But, for info,
- Workbook_SheetChange (which goes in ThisWorkbook module) applies to all sheets (or specific sheets if required) and can avoid the need for identical Worksheet_Change being replicated for multiple sheets
- your sheets are different to each other and so this is irrelevant

However
- if all your client are identical, there may be is a case for a central repository for some of the code, instead of replicating across multiple workbooks

Yongle, I'm sorry for the delay, I had a few unexpected projects come up and am just now getting back to this original question. Below is the code I'm now using to try to hide Column W and Rows 69-108 automatically based on Cell w10 (column) and based on value in column B for each independent row (rows 69-108). This is not working though and am wondering if you could still help? I attempted to take out the application.intersect command, but still couldn't get the script to run based on all workbook changes. Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells(1).Value = True Then
            Me.Columns("w").Hidden = False
        Else
            Me.Columns("w").Hidden = True
        End If
                If Target.Cells(b69:b108).Value = "" Then
            Me.Rows().Hidden = False
        Else
            Me.Rows().Hidden = True
        End If
End Sub

 

[StorminASU]

Yongle, any other recommendations you can offer? If not, I definitely understand and thanks so much for your help thus far!