Combining IF with LEFT to hide columns VBA

[danno79]

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

 

[danno79]

Really sorry VoG - getting myself all mixed up here. I made a mistake in my initial post - I should have said I want to hide columns that don't contain the same first 3 chars as irow=1, icol=1

 

[VoG]

Then try

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range, cell As Range
Dim iRow As Long, iCol As Integer

Set r = Me.Range("B7:CG7")
Application.ScreenUpdating = False
Application.EnableEvents = False

iRow = 1
iCol = 1

For Each cell In r
    cell.EntireColumn.Hidden = cell.Value <> "" And Left(cell.Value, 3) <> Cells(iRow, iCol).Value
Next cell
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[danno79]

Excellent - thank you fopr your help!