VBA code not hiding columns based on cell values

[Stuepef]

I have a VBA code the hides columns based on 3 different cell values, but it is not working properly. If cell Z7 & Z33 do not equal "Black" AND if cell Z39 does not equal "Yes" then hide columns K:N. So I want the code to hide columns K:N if Z7 & Z33 <> "Black" and Z39 <> "Yes".

Sub Test()Application.ScreenUpdating = False
      With Sheet10
        If Sheet1.Range("Z7, Z33") <> "Black" And Sheet1.Range("Z39") <> "Yes" Then
        .Columns("K:N").EntireColumn.Hidden = True
        End If
      End With
   Application.ScreenUpdating = True
End Sub

 

[JoeMo]

Change this bit:

If Sheet1.Range("Z7, Z33") <> "Black"

to this:

If Sheet1.Range("Z7") <> "Black" And Sheet1.Range("Z33") <> "Black"

 

[Stuepef]

I tried the updated code and received the same result. The example I tried had both Z7 & Z33 = "Black", but Z39 was no, which should have hid columns K:N. Does the code have to be re-written to account for a specific order?

Sub Test()Application.ScreenUpdating = False
      With Sheet10
        If Sheet1.Range("Z7") <> "Black" And Sheet1.Range("Z33") <> "Black" And Sheet1.Range("Z39") <> "Yes" Then
        .Columns("K:N").EntireColumn.Hidden = True
        End If
      End With
   Application.ScreenUpdating = True
End Sub

 

[JoeMo]

I tried the updated code and received the same result. The example I tried had both Z7 & Z33 = "Black", but Z39 was no, which should have hid columns K:N. Does the code have to be re-written to account for a specific order?

Sub Test()
Application.ScreenUpdating = False
      With Sheet10
        If Sheet1.Range("Z7") <> "Black" And Sheet1.Range("Z33") <> "Black" And Sheet1.Range("Z39") <> "Yes" Then
        .Columns("K:N").EntireColumn.Hidden = True
        End If
      End With
   Application.ScreenUpdating = True
End Sub

The code you posted works fine for me to hide those cols in Sheet10 based on the three conditions in Sheet1. You do realize that if cols K:N are already hidden, your code will not restore them if one of the three conditions is not true, yes??

and ..... the specific order of the three conditions is irrelevant.

 

[Stuepef]

Yes the columns will always start unhidden. I experimented a little with the code and it will not hide the columns if one of the criteria is true. For instance, Z7 could be yellow, but Z33 is Black so it wont hide the columns because the second criteria is true. With that being said, does my code have to be rewritten to account for this?

 

[JoeMo]

Yes the columns will always start unhidden. I experimented a little with the code and it will not hide the columns if one of the criteria is true. For instance, Z7 could be yellow, but Z33 is Black so it wont hide the columns because the second criteria is true. With that being said, does my code have to be rewritten to account for this?

The bold part of your reply is not correct. It won't hide the columns because one of the criteria IS NOT TRUE.

I'm not at all clear on exactly what you want your code to do. If the columns are not already hidden, all three criteria must be true to hide them. If the columns are already hidden, if any of the criteria is false, the columns will not be unhidden. Is that what you want? If not, please explain what exactly you want the code to do.

 

[Stuepef]

Ha I definitely confused myself on this one, my apologies - need more coffee! Thank you for your patience.

 

[JoeMo]

Ha I definitely confused myself on this one, my apologies - need more coffee! Thank you for your patience.

You are welcome - thanks for the reply.