Hiding columns in Excel based on cell value using VBA macro doesn't work if cell value is result of formula

I know very little/next to nothing about Excel macros. Just so you know where I'm coming from.

I tried searching for a way to hide/unhide columns based on the text value of a reference cell, and found examples using VBA macros. This works very well if the reference cell value is in plain text, but if I generate the same text using a formula in the reference cell, it doesn't work.

E.g. if the reference cell contains the trigger text string "A" it works, but if the reference cell contains a formula on the lines of =if(something then "A"), it doesn't work.

A plain "A" triggers the hiding/unhiding, but a formula-generated "A" does not.

Is there a way to trigger on formula-generated text strings?

This is the code I found in a similar question asked here, adapted to my sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim allColumns As Range

    Set allColumns = Columns("AG:AI")
    allColumns.Hidden = True

    If Not Intersect(Target, Range("AW3")) Is Nothing Then
        If Target.Value = "Test" Then
            Columns("AG:AI").Hidden = False
        End If
    End If
End Sub

Columns to be hidden/unhidden are AG:AI, and the trigger cell is AW3, set to the trigger word "Test".

If I type "Test" in the reference cell, it works, but if I enter the following formula, which generates the output string "Test", it doesn't work:

=IF(ISBLANK(INDIRECT("AC"&MATCH(TRUE|ISBLANK($C$5:$C$29)|0)+3))|"Ställning just nu"|IF(AND(BC5="Guldet klart"|BC6="Silvret klart"|BC7="Bronset klart")|"Slutställning"|"Test"))

Solution

Formulas do not trigger a Worksheet_Change when they recalculate, so your Target.Value = "Test" will never be true because Target will never be Range("AW3").

Target is the range of cells the changes in which have caused the current invocation of Worksheet_Change. It can consist of multiple cells, and you should check whether it does before trying to access such properties as Value.

It would seem you don't have a specific range of cells changes in which you want to track because they could affect the calculation in AW3. That means you want to check the contents of AW3 regardless of what Target is:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim allColumns As Range

    Set allColumns = Columns("AG:AI")
    allColumns.Hidden = True

    If Me.Range("AW3").Value = "Test" Then
        Columns("AG:AI").Hidden = False
    End If
End Sub

Or, as suggested in a comment to your question,

Private Sub Worksheet_Change(ByVal Target As Range)
    Columns("AG:AI").Hidden = Not Me.Range("AW3").Value = "Test"
End Sub