Run VBA on cell value from VLookup

[Lord_B]

Hi,

I have checked the forum but cant seem to find a solution that works.

I need to run the below code when one of the cell values change.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("uk1").Value = "a" Then
Range("A8:a47").EntireRow.Hidden = False
Else
Range("A8:a47").EntireRow.Hidden = True
End If
If Range("uk2").Value = "a" Then
Range("A48:A87").EntireRow.Hidden = False
Else
Range("A48:A87").EntireRow.Hidden = True
End If
If Range("uk3").Value = "a" Then
Range("A88:A127").EntireRow.Hidden = False
Else
Range("A88:A127").EntireRow.Hidden = True
End If
If Range("uk4").Value = "a" Then
Range("A128:A167").EntireRow.Hidden = False
Else
Range("A128:A167").EntireRow.Hidden = True
End If
If Range("uk5").Value = "a" Then
Range("A168:A207").EntireRow.Hidden = False
Else
Range("A168:A207").EntireRow.Hidden = True
End If
End Sub

The code works if I manually change the above cell values but not when I use VLookup to get the value.

Your help on this will be much appreciated.

Ben

 

[AlphaFrog]

What cell(s) do you manually change to recalculate the VLOOKUP formula? Can you post the VLOOKUP formula and describe the layout?

 

[Lord_B]

The formulas are in the following cells;

Cell | Formula
UK1 =IF(ISNA(VLOOKUP(calc!LN4,calc!LW4:LX12,2)),"r",VLOOKUP(calc!LN4,calc!LW4:LX12,2))
UK2 =IF(ISNA(VLOOKUP(calc!LO4,calc!LW4:LX12,2)),"r",VLOOKUP(calc!LO4,calc!LW4:LX12,2))
UK3 =IF(ISNA(VLOOKUP(calc!LP4,calc!LW4:LX12,2)),"r",VLOOKUP(calc!LP4,calc!LW4:LX12,2))
UK4 =IF(ISNA(VLOOKUP(calc!LQ4,calc!LW4:LX12,2)),"r",VLOOKUP(calc!LQ4,calc!LW4:LX12,2))
UK5 =IF(ISNA(VLOOKUP(calc!LR4,calc!LW4:LX12,2)),"r",VLOOKUP(calc!LR4,calc!LW4:LX12,2))

From the VBA originally posted, if the above VLookup places “a” in the cell then the required rows need to be hidden, but this does not work.

If I manually change the cell value to “a” the rows are automatically hidden.

So if cells UK1, UK2 = “a” the rows A8 – A87 should be visible with rows A88 – A207 hidden.

 

[AlphaFrog]

Put this code in the calc worksheet code module. Change the Sheet2 in the code to the worksheet that has the Vlookup formulas.

When you manually change the LN4:LR4 values on the calc sheet, that will trigger the code to check the results on the other sheet and hide\unhide rows.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Target, Range("LN4:LR4")) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]With[/COLOR] Sheets("[B]Sheet2[/B]")
            .Range("A8:a47").EntireRow.Hidden = .Range("uk1").Value <> "a"
            .Range("A48:A87").EntireRow.Hidden = .Range("uk2").Value <> "a"
            .Range("A88:A127").EntireRow.Hidden = .Range("uk3").Value <> "a"
            .Range("A128:A167").EntireRow.Hidden = .Range("uk4").Value <> "a"
            .Range("A168:A207").EntireRow.Hidden = .Range("uk5").Value <> "a"
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[Lord_B]

Thank you, I love the way you shrank the code down to hand full of lines!!

Thank you this helped