Worksheet Macros and Combining Them to 1

[cmschmitz24]

Can someone help me with combining the following two macros?
I cannot seem to figure out what I need to change/update.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address <> "$B$3" Then Exit Sub
    Application.EnableEvents = False
    
    If Target.Value = "Married" Then
    'Hides Federal Single
        Cells.EntireColumn.Hidden = False
        Columns("H:H").Hidden = True  'Hides Federal Single - State Single
        Columns("J:J").Hidden = True  'Hides Federal Single - State Married
        Columns("I:I").Hidden = False 'Unhides Federal Married - State Married
        Columns("K:K").Hidden = False 'Unhides Federal Married - State Single

    End If
    
    If Target.Value = "Single" Then
    'Hides Federal Married
        Cells.EntireColumn.Hidden = False
        Columns("H:H").Hidden = False 'Unhides Federal Single - State Single
        Columns("J:J").Hidden = False 'Unhides Federal Single - State Married
        Columns("I:I").Hidden = True  'Hides Federal Married - State Married
        Columns("K:K").Hidden = True  'Hides Federal Married - State Single

    End If
    
    Application.EnableEvents = True
    End Sub
    
Private Sub Worksheet_Change2(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address <> "$B$4" Then Exit Sub
    Application.EnableEvents = False
    
    If Target.Value = "Married" Then
    'Hides State Single
        Cells.EntireColumn.Hidden = False
        Columns("H:H").Hidden = True   'Hides Federal Single - State Single
        Columns("J:J").Hidden = False  'Unhides Federal Single - State Married
        Columns("I:I").Hidden = False  'Unhides Federal Married - State Married
        Columns("K:K").Hidden = True   'Hides Federal Married - State Single

    End If
    
    If Target.Value = "Single" Then
    'Hides State Married
        Cells.EntireColumn.Hidden = False
        Columns("H:H").Hidden = False   'Hides Federal Single - State Single
        Columns("J:J").Hidden = True    'Unhides Federal Single - State Married
        Columns("I:I").Hidden = True    'Unhides Federal Married - State Married
        Columns("K:K").Hidden = False   'Hides Federal Married - State Single
    End If
    
    Application.EnableEvents = True
    
End Sub

 

[Michael M]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("B3, B4")) Is Nothing Then Exit Sub
    If Target.Value = "Married" Then
    'Hides Federal Single
        Cells.EntireColumn.Hidden = False
        Columns("H:H").Hidden = True  'Hides Federal Single - State Single
        Columns("J:J").Hidden = True  'Hides Federal Single - State Married
        Columns("I:I").Hidden = False 'Unhides Federal Married - State Married
        Columns("K:K").Hidden = False 'Unhides Federal Married - State Single

    End If
    
    If Target.Value = "Single" Then
    'Hides Federal Married
        Cells.EntireColumn.Hidden = False
        Columns("H:H").Hidden = False 'Unhides Federal Single - State Single
        Columns("J:J").Hidden = False 'Unhides Federal Single - State Married
        Columns("I:I").Hidden = True  'Hides Federal Married - State Married
        Columns("K:K").Hidden = True  'Hides Federal Married - State Single

    End If
    
    Application.EnableEvents = True
    End Sub

 

[cmschmitz24]

Hi Michael,

This does not work to what I'm expecting.

I have two drop down lists: B3 and B4
B3 is for Federal single or married
B4 is for State single or married

I have 4 columns that is a combination of B3 and B4.
I want all columns to hide if not the combination that the two drop down lists create.
Column H: Single/Single
Column I: Married/Married
Column J: Single/Married
Column K: Married/Single

Example:
B3: Single
B4: Married

I want the column that shows Federal Single/State Married to be the only column that shows. So column J would be the only column unhidden.


Thanks,
Christina

 

[Michael M]

Try then

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("B3, B4")) Is Nothing Then Exit Sub
    If Range("B3").Value = "Married" And Range("B4").Value = "Married" Then
        Cells.EntireColumn.Hidden = False
        Columns("H:H").Hidden = True  'Hides Federal Single - State Single
        Columns("J:J").Hidden = True  'Hides Federal Single - State Married
        Columns("K:K").Hidden = True 'Unhides Federal Married - State Single
    End If
    If Range("B3").Value = "Single" And Range("B4").Value = "Single" Then
        Cells.EntireColumn.Hidden = False
        Columns("J:J").Hidden = True  'Hides Federal Single - State Married
        Columns("I:I").Hidden = True  'Hides Federal Married - State Married
        Columns("K:K").Hidden = True 'Unhides Federal Married - State Single
    End If
    If Range("B3").Value = "Single" And Range("B4").Value = "Married" Then
        Cells.EntireColumn.Hidden = False
        Columns("H:H").Hidden = True 'Unhides Federal Single - State Single
        Columns("I:I").Hidden = True  'Hides Federal Married - State Married
        Columns("K:K").Hidden = True  'Hides Federal Married - State Single
    End If
    If Range("B3").Value = "Married" And Range("B4").Value = "Single" Then
        Cells.EntireColumn.Hidden = False
        Columns("H:H").Hidden = True 'Unhides Federal Single - State Single
        Columns("J:J").Hidden = False 'Unhides Federal Single - State Married
        Columns("I:I").Hidden = True  'Hides Federal Married - State Married
    End If
    Application.EnableEvents = True
    End Sub

 

[cmschmitz24]

Hi Michael,

Thank you, this one works perfect.

I now have a new question -

Is there an if then statement that would be able to pull data based on the drop down combination?

Example:
Sheet1 cell C28 needs to pull the data that is in the corresponding cell based on the combination of the drop down cell.

C28 on Sheet 1 should show the value populated in Sheet 2 cell H8 if the drop down states Federal Single/State Single.
The drop down lists are on the 2nd sheet.

C28 on Sheet 1 should show the value populated in Sheet 2 cell I8 if the drop down states Federal Married/State Married.

Etc..

Thanks,
Christina