Forum Discussion

Jenstarzie's avatar
Jenstarzie
Copper Contributor
Apr 30, 2025

VBA Coding Help - Multiple Criteria 'If Range'

Hi, 

I'm relatively new to VBA in Excel and working on some improvements to a form I have created. 
As part of this, i would like a panel on the bottom of the form to unhide certain rows based on sets of criteria in other cells. I have written out coding that i thought would work but it does not produce the required result so hoping someone here can help me correct it and show where I am going wrong please?

 

AIM: 

  • Approval panel rows 47:49 and 51 unhide when any of the following is true - Cell D9 <450001 or cell D10<32000001 or cell H9<75001 or cell H10<50001 (rows 50 and 52:53 remain hidden)
  • Or Approval panel rows 47:51 unhide when any of the following is true - Cell D9 >450000 but <1000001 or cell D10<32000001 or cell H9>75000 but <200001 or cell H10>50000 but <100001 (rows 52:53 remain hidden) 
  • Or Approval panel rows 47:53 unhide when any of the following is true - Cell D9 >1000000 but <7900001 or cell D10>32000000 but <39400001 or cell H9>200000 but <3900001 or cell H10>100000 but <236001 

 

The current code i have is: 

If Range("D9").Value <= 450001 Or Range("D10").Value <= 32000001 Or Range("H9").Value <= 75001 Or Range("H10").Value <= 50001 Then
    Rows("47:49").EntireRow.Hidden = False
    Rows("50").EntireRow.Hidden = True
    Rows("51").EntireRow.Hidden = False
    Rows("52:53").EntireRow.Hidden = True
ElseIf Range("D9").Value >= 450000 And Range("D9").Value <= 1000001 Or Range("D10").Value <= 32000001 Or Range("H9").Value >= 75000 And Range("H9").Value <= 200001 Or Range("H10").Value >= 50001 And Range("H10").Value <= 4100001 Then
    Rows("47:51").EntireRow.Hidden = False
    Rows("52:53").EntireRow.Hidden = True
ElseIf Range("D9").Value >= 1000000 And Range("D9").Value <= 7900001 Or Range("D10").Value >= 32000000 And Range("D10").Value <= 39400001 Or Range("H9").Value >= 200000 And Range("H9").Value <= 3900001 Or Range("H10").Value >= 100000 And Range("H10").Value <= 236001 Then
    Rows("47:53").EntireRow.Hidden = False
End If

 

If it makes any difference, the cells containing the criteria (D9, D10, H9 & H10) are all formatted to Currency £ with 2 decimal places, and this needs to remain. 

 

I'd really appreciate if anyone can help me work this out. 

Thank you in advance for your time and help. 

 

developer
excel
Formulas and Functions
Macros and VBA

3 Replies

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Apr 30, 2025

    So what is happening wrong?  My first step would be to add ( ) around all the groups.  you have a string of A AND B OR C AND D OR ... and I wouldn't be surprised if that is not acting the way you think / hope it is.

    • Jenstarzie's avatar
      Jenstarzie
      Copper Contributor
      May 01, 2025

      Hi, Thanks for your reply. Nothing happens at all at the moment so there is obviously something in the code that isn't registering. I will try adding the brackets around the groups and report back on how that goes. 

      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        May 01, 2025

        so i threw the code into a vba sheet and it seems to "work".

        A few tips and good practices to consider include 

        • defining the range object (e.g. ActiveSheet.Range.... or ActiveWorkbook.Sheets("MySheet").Range...)
        • Use Breaks (click on gray bar to left of code to toggle break points) and Step through code (F8)
        • Use the immediate window to 'try' things out (e.g. PRINT Activesheet.range("D9").value)
        • highlight parts of the code and hover over it to see immediate value where possible 

        notice in this image I have a breakpoint on the IF, then hit F8 to move to the next line and highlighted the text/code "Range("D9").Value <= 450001" and hovered over it to see the tooltip/pop-up window showing that it evaluates to False

         

        hope that helps

Resources