Select Named Range , depend on Cell Value

[Rahul14181]

Hello Everyone,

I need some urgent help regards to the subjected query: "Select Named Range , depend on Cell Value"

I have a searchable drop down at A1 and there is a button.

I have given name to specific range as below:

B5:C30 - Rahul
E5:D30 - Arvind
F5:G30 - Kumar

In drop down we have three name, (Rahul/Arvind/Kumar)... I want when A1 is Rahul and i click on button then B5:C30 get selected and vise versa for arvind and kumar too..

Please help

 

[ttray33y]

try:

Sub test()


Application.ScreenUpdating = False


Dim Ash As Worksheet
Dim stxt1 As String
Dim stxt2 As String
Dim stxt3 As String


Set Ash = ActiveWorkbook.ActiveSheet


stxt1 = "Rahul"
stxt2 = "Arvind"
stxt3 = "Kumar"


    If Ash.Cells(1, 2) = stxt1 Then
        With Ash
            .Range(.Cells(2, 5), .Cells(3, 30)).Select
        End With
    End If
    
    If Ash.Cells(1, 2) = stxt2 Then
        With Ash
            .Range(.Cells(4, 5), .Cells(5, 30)).Select
        End With
    End If
    
    If Ash.Cells(1, 2) = stxt3 Then
        With Ash
            .Range(.Cells(6, 5), .Cells(7, 30)).Select
        End With
    End If
   
Application.ScreenUpdating = True




End Sub

 

[ttray33y]

OR

Sub test()


Application.ScreenUpdating = False


Dim Ash As Worksheet
Dim stxt1 As String
Dim stxt2 As String
Dim stxt3 As String


Set Ash = ActiveWorkbook.ActiveSheet


stxt1 = "Rahul"
stxt2 = "Arvind"
stxt3 = "Kumar"


    If Ash.Range("A1") = stxt1 Then
        With Ash
            .Range("B5:C30").Select
        End With
    End If
    
    If Ash.Range("A1") = stxt2 Then
        With Ash
            .Range("D5:E30").Select
        End With
    End If
    
    If Ash.Range("A1") = stxt3 Then
        With Ash
            .Range("F5:G30").Select
        End With
    End If
   
Application.ScreenUpdating = True




End Sub

 

[Rahul14181]

Thank you so much...!! its works..

But have one small query. i have a big data. so if i also want cursor to go highlighted range then what i have to do..

for example if i change the range from B5:C30 to B100:C100 in this scenario mentioned cell will get highlighted i m still on the top of the window no on B100..

 

[ttray33y]

just replace .Select to .Interior.ColorIndex = 3

 

[Rahul14181]

I am sorry but i think i mentioned my question in wrong way.

I want that when i click on button, it should take me to that selected name range.

 

[ttray33y]

this one has variable row numbers that can be entered in B1 and C1, type any number on the cells and hit run.

Sub test()


Application.ScreenUpdating = False


Dim Ash As Worksheet
Dim stxt1 As String
Dim stxt2 As String
Dim stxt3 As String


Set Ash = ActiveWorkbook.ActiveSheet


stxt1 = "Rahul"
stxt2 = "Arvind"
stxt3 = "Kumar"
rnum = ActiveSheet.Range("B1").Value
cnum = ActiveSheet.Range("C1").Value


    If Ash.Cells(1, 1) = stxt1 Then
        With Ash
            .Range(.Cells(rnum, 2), .Cells(cnum, 3)).Select
        End With
    End If


    If Ash.Cells(1, 1) = stxt2 Then
        With Ash
            .Range(.Cells(rnum, 4), .Cells(cnum, 5)).Select
        End With
    End If


    If Ash.Cells(1, 1) = stxt3 Then
        With Ash
            .Range(.Cells(rnum, 6), .Cells(cnum, 7)).Select
        End With
    End If


Application.ScreenUpdating = True


End Sub