VBA Hide columns

[blossomthe2nd]

Hi

I have a spread sheet has columns A-Z populated with headers.

I wish to write a code that will highlight certain columns based on a drop down in cell B1, see below.

*Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B1").Value = "Red" Then
Columns("H:Z").EntireColumn.Hidden = True
Else
Columns("H:Z").EntireColumn.Hidden = False
End If
End Sub
*

This is working fine, but there are a few different combinations i need to use.

Is it possible to amend the above to state if range is equal to RED OR BLUE OR GREEN hide H:Z ?

And if so How ?

Thanks

 

[Weaver]

Have a look at the instr() function.

if instr("red blue green",lcase(range("b1").value))>0 then
  'do things here
end if

Should work

 

[blossomthe2nd]

Thanks Weaver,

Can I ask ? Some of the drop downs are 2 words like Light green, would this look like ---

if instr("red blue green light green",lcase(range("b1").value))>0 then
'do things here
end if

Or do I need _ ?

Thanks
A

 

[Weaver]

If possible, I'd be tempted to go with the underscore, especially if say you didn't want the columns to hide for "green" but you did for "light green". You'll have to change the dropdowns as well although I'm sure you already realised this. If this is a problem, there are other ways of achieving the same result.

 

[blossomthe2nd]

Hi Weaver ,

I've tried the folloing and it isnt working ?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If InStr("Out_Of_Course Drawdowns Customer_Service", LCase(Range("B2").Value)) > 0 Then
Columns("C:G").EntireColumn.Hidden = True
Else
Columns("C:G").EntireColumn.Hidden = False
End If
End Sub


I'm very new to this -- Have any advice ?

 

[blossomthe2nd]

Apologies Weaver

Think Its running now