Using Value in Cell to hide/unhide rows

Hi Ayub

Sorry - I've been busy at work and not been on the forum for a good week or so.

In my code above (Post #20) you can add this requirement. The new code becomes:

If Target.Address = "$H$10" Then
      With Range("E418").Validation
        .Delete
     'Change Validation to match Value
      If Range("h10").Value = 3 Or Range("h10").Value = 4 Or _
       (Range("H10").Value = 2 And Range("W10").Value = "Weld Surface") Then
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
         xlBetween, Formula1:="Continuous Wet Fluorescent"
        'Set E418 to CWF if 3 or 4
         Range("E418").Value = "Continuous Wet Fluorescent"
         Else
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
         xlBetween, Formula1:="Continuous Wet,Continuous Wet Fluorescent"
         End If
      End With
    End If

This is the line that has changed:

      If Range("h10").Value = 3 Or Range("h10").Value = 4 Or _
       (Range("H10").Value = 2 And Range("W10").Value = "Weld Surface") Then

Notice the addition of the (... And ...). This is how you check two cells at once. Also notice, we are not using the Target here, just normal Range statements.

Lastly - did you spot the underscore after the Or: _

This allows you to continue writing the code on the next line. It is treated as one line of code. This is useful when you have a long piece of code that scrolls past the end of the edit window, or you want to separate out pieces of code that are related, such as a bunch of Or/And statements.

I don't have an up to date copy of the spreadsheet, so I can't incorporate it for you. If you get stuck, upload the sheet and I'll have a look.

Best regards, Rob.

Hi Rob,

Sorry but i have been also busy with work so i had no time to reply to your post. You have provided me a good way to tie in 2 conditional statements and a way to reduce a long line of the codes. Will be helpful in writing macros in the future. Thanks to your help, i was able to make many refinements to the form since the last time. I will incorporate the new codes and upload it here within this week or so.

A big thanks,

Ayub

Hi rob,

As promised i've attached a copy of the fully modified and functional form that i've been working on. There is a small issue that i was hoping you can guide me, once again. In the form, under the turquoise field is a drop down menu. There are a few hundred values in the list from M1XXX rev. X to M4XXX rev.X. I'm trying to write another macro that will be able to identify only those values from M3XXX rev. X to M4XXX rev. X and use it to hide the whole MPI section of the form only. I now need to know how to just detect the first 2 characters from the list, afterwhich i can incorporate the hide rows codes.

QAQC-FORM.xlsm


Thanks a million,

Ayub

Any other forum users who may help me to write a macro to detect the first 2 characters in the dropdown menu to effect an action in the worksheet?