VBA - hiding column based on value of cell

[tkaplan]

Hi,
i'm trying to do something that i believe is really simple, and for some reason i can't get it to work.
i have a named range in my excel file named ReductionType in Sheet1. I want columns I:J in Sheet2 to be hidden if the text in reductionType is "One Time". otherwise i want columns G:H hidden.

I have:


Sub Macro3() 'macro is called from Sheet2
Columns("G:J").EntireColumn.Hidden = False 'make sure all columns are unhidden
Dim reduction As String
reduction = Range("ReductionType") 'have also tried this with .value appended to the end of this line
IIf reduction = "One Time Premium Reduction", Columns("I:J").enitrecolumn.Hidden = True, Columns("G:H").EntireColumn.Hidden = True
End Sub

and i'm getting errors.
any ideas please? I'm at a loss....

thanks!

[BIFanatic]

Assuming the named range refers to Range A1 in Sheet1, this code need to sit in the Sheet1's code Module, easiest way to get there is to right click in the name tab in the bottom and click view code.




Private Sub Worksheet_Change(ByVal Target As Range)


Dim MyRange As Range
Dim TargetRange As Range


Set MyRange = Me.Range("ReductionType")
Set TargetRange = Sheet2.Range("G:H")

If MyRange.Value = "One Time" Then
TargetRange.EntireColumn.Hidden = True
Else
TargetRange.EntireColumn.Hidden = False
End If


End Sub

[tkaplan]

thank you so much for this! can't believe i forgot If Then syntax and did as IIF! i knew it has been a while since i worked in code....but now I'm embarrassed LOL.

Thanks for your help!