Hi Guys
I'm trying to write some code to Hide columns if the first 3 characters of cells in a range equal the contents of another (formula equivalent would be something like this =IF((LEFT(A2,3))=A1,"HIDE COLUMN", "SHOW COLUMN"). I have gotten this far but cant get it to work;-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, cell As Range
On Error GoTo ErrHandler
Set r = Me.Range("B7:CG7")
Application.ScreenUpdating = False
Application.EnableEvents = False
Row = 1
col = 1
For Each cell In r
If cell.Value = "" And Left(cell.Value, 3) = cell(Row, col).Value Then
cell.EntireColumn.Hidden = True
Else
cell.EntireColumn.Hidden = False
End If
Next
ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Think the problem is in the if statement - any help greatly appreciated
Cheers
I'm trying to write some code to Hide columns if the first 3 characters of cells in a range equal the contents of another (formula equivalent would be something like this =IF((LEFT(A2,3))=A1,"HIDE COLUMN", "SHOW COLUMN"). I have gotten this far but cant get it to work;-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, cell As Range
On Error GoTo ErrHandler
Set r = Me.Range("B7:CG7")
Application.ScreenUpdating = False
Application.EnableEvents = False
Row = 1
col = 1
For Each cell In r
If cell.Value = "" And Left(cell.Value, 3) = cell(Row, col).Value Then
cell.EntireColumn.Hidden = True
Else
cell.EntireColumn.Hidden = False
End If
Next
ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Think the problem is in the if statement - any help greatly appreciated
Cheers