Data Valadation List Zooming

[Boomer_Sooner]

I have a lot of cells that have lists that I would like to put into a code in order to have the zoom increase when those specific cells are active. Below is the code I am using and the error is with the list. I believe it is because it is too long. How can I make this work? The list in parenthesis is all on one line.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range( _
"F4,B14,D14,B22,D22,B30,D30,B38,D38,B46,D46,B57,D57,B65,D65,B73,D73,B81,D81,B89,D89,B104,D104,B111,D111,B119,D119,B127,D127,B135,D135,B143,D143,B151,D151,B159,D159,B163,D163,B171,D171,B179,D179,B187,D187,B195,D195,G13:G29,H33,J33,H41,J41,H49,J49,H57,J57,H65,J65,H73,J73,H81,J81,H85,J85,H93,J93,H101,J101,H109,J109,H113,J113,G121,G132,H141,J141,H149,J149,H157,J157,H165,J165,H173,J173,H181,J181,H189,J189,H197,J197" _
)) Is Nothing Then
ActiveWindow.Zoom = 100
Else
ActiveWindow.Zoom = 120
End If
End Sub

Thanks in advance for the help.
Chris

 

[Boomer_Sooner]

It gives me a Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

error when clicking on any cell

 

[StephenCrump]

Welcome to the Forum!

Yes, as you suspected, your range name is too long (>255 characters).

One simple fix would be to split the range into two, and check whether the Intersect of Target and each sub-range Is Nothing.

However, this still involves hard-coding an awfully large number of cell references. Your code will need manual updating if you insert rows or columns, or add/delete validation lists.

If you want to zoom for all cells with list validation, you could use:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim cell As Range
    Dim lValidationType As Long
    
    Application.ScreenUpdating = False
    ActiveWindow.Zoom = 100
    On Error Resume Next
        
    For Each cell In Target
        lValidationType = cell.Validation.Type
        If lValidationType = xlValidateList Then
            ActiveWindow.Zoom = 120
            Exit For
        End If
    Next cell
    
    Application.ScreenUpdating = True
    On Error GoTo 0
    
End Sub

If list validation is the only validation you are using in the sheet, you could simplify to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Application.EnableEvents = False
    If Intersect(Target, Cells.SpecialCells(xlCellTypeAllValidation)) Is Nothing Then
        ActiveWindow.Zoom = 100
    Else
        ActiveWindow.Zoom = 120
    End If
    Application.EnableEvents = True
    
End Sub

 

[kvsrinivasamurthy]

Try this code

If Intersect(Target, Range("F4")) Is Nothing Or Target.Column = Range("B1").Column And (Target.Row - 14) Mod 8 = 0 And Target.Row <= 197 _   Or Target.Column = Range("D1").Column And (Target.Row - 14) Mod 8 = 0 And Target.Row <= 197 Then
   [COLOR=#333333]ActiveWindow.Zoom = 100[/COLOR]
Else
ActiveWindow.Zoom = 120
End If

 

[Boomer_Sooner]

Thanks to all those that answered. I ended up going with a color code and that works great!!! I never would have thought about doing that.