Working selectively with worksheet_change

[wobbles]

I have (with help) managed to get the following code working based on whether my target value is a * or not. This works a treat.

I now find myself in a situation where i dont have enough control of the routine to additionally decide when the code should run
my comment in the vba code attached explain more easily what i am trying to do
I would also like to replace the input boxes with a form

May I PLEASE ask if anyone submits code (which i hope you will) that they remark it otherwise i am never going to learn

Thank you in advance

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ws_exit
 
 
    Application.EnableEvents = False
 
    With Target
    ' I need a further condition then ensures the trigger column is one that needs this done
    ' the column at row 8 will be marked with another * but hidden with a white font on white
    ' then to complicate it further the row number must be greater than or equal to 9 and odd
    ' Finally cell(L3) should indicate a valid row and column with for example the word valid
    '
        If .Value = "*" Then
 
            '
            ' This Should run if the user is to choose * from the drop down list
            '
            Dim Start_time As String ' Set Shift Start Time
            Dim Finish_time As String ' Set Shift Finish Time
            Start_time = Inputbox("Start Time", "MANUAL ENTRY MODE 1 of 2") ' Input The Start Time
            .Offset(0, 1).Value = Start_time ' Update cell with the entered start value
            Finish_time = Inputbox("Finish Time", "MANUAL ENTRY MODE 2 of 2") ' Input The Finish Time
            .Offset(0, 2).Value = Finish_time ' Update the cell with the entered Finish time
        Else
 
            .Offset(0, 1).Formula = "=VLOOKUP(" & .Address & ",$BE$9:$BH$20,3,FALSE)"
            .Offset(0, 2).Formula = "=VLOOKUP(" & .Address & ",$BE$9:$BH$20,4,FALSE)"
        End If
    End With
ws_exit:
    Application.EnableEvents = True
End

 

[rcbunting]

Replace your condition with this:

If .Value = "*" And Cells(8, .Column()).Value = "*" And .Row() >= 9 _
And .Row() Mod 2 = 1 Then

Ok, here's what's happening - apologies if it is too basic, no insult intended (you mentioned that you wanted to learn)

Since you want all of your conditions to be true, we join every logical test with AND. If you don't grasp the difference between logical AND and logical OR, you should spend some time reading up on it. Search the web for 'Boolean Operators.'

First, we need to check to see if the value of the target cell is "*"

.Value = "*"

Next, look to see if the cell at row 8 in the same column as our target cell is also "*". The Cells function lets us examine a cell at a specific row and column. We know it's row 8 -- that's a given. We know that we're looking in the same column as our target, so we can use the column from that.

Cells(8, .Column()).Value = "*"

Third, target's row number has to be greater than or equal to 9. Easy.

.Row() >= 9

Fourth, target's row has to be an odd number. Modulus is the way to figure this out. If you're not familiar with the operation, basically think back to long division. Modulus returns the remainder only. So, 5 Modulus 2 would return 1, because 5 divided by 2 would be 2 R 1. 11 mod 3 = 2, etc. If you want to know if a number is odd, divided it by 2. If there's a remainder, it's odd. So, if N mod 2 = 1, then N is odd.

.Row() Mod 2 = 1

Stich them all together with AND -- you end up with

If .Value = "*" AND Cells(8, Target.Row()).Value = "*" AND Target.Row() >= 9  _
AND Target.Row() Mod 2 = 1 Then

 

[wobbles]

Thank you rcbunting and no i am not offended, your step by step explanation is fantastic.
I was aware of some of the elements of your comments BUT i was really struggling to peice them together into something useable.

compared to my solution of four IF and three of which exited the sub yours shows me the way to think

Now i just need to master moving around cells in vba without losing where I was.

Much apprechiated

 

[wobbles]

UPDATE :

Your code didnt work

This does though but its sloppy

    If .Row Mod 2 <> 1 Then Exit Sub
    If Cells(8, .Column) <> "*" Then Exit Sub
    If .Row < 9 Then Exit Sub
    If .Value = "*" Then
        ' This Should run if the user is to choose * from the drop down list

Dunno why

 

[Smitty]

Welcome to the Board!

Here's some boilerplate Change event code that shows you how to limit the event to certain ranges:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("xxx")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,