VBA - Change Data Validation drop down value based on a value

[ChrisMac1]

Hi there, and thanks for looking at my issue for me.

I have a workbook that has a sheet called DATA. On that sheet I want a code that will revise the data validation dropdown value in column AH from it's blank default to EASY WIN, but only if the value in column B says NO SHOW and the date value in column S is +60 days from today.

I hope I have explained that ok and that this has an easy solution.

Many thanks,
Chris Mac

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[ChrisMac1]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Sorry, the doc has confidential data in so I cannot provide a copy. I can create a dummy doc if that would help?

 

[mumps]

The dummy doc is fine as long as the data is organized in exactly the same way as in the original.

 

[ChrisMac1]

Thanks for the speedy response mumps, and FYI I am in the UK, in case that makes any difference to anything

Unfortunately, due to restrictions on my work laptop I cannot install new apps and cannot access certain sites, including DropBox. Best I can do is a lame screen shot, which is rubbish I know.

So looking at the DATA sheet, when the macro runs it would scan all entries from row 2 to row 100000, checking for any example of a NO SHOW in column B. If a NO SHOW is found the macro would look at the date in column S of that row. If the date is 60 days or older the macro would revise the entry in the data validation option in column AH to be EASY WIN.

On my doc, the only row that would change would be in row 2.

I hope that helps, sorry, all a bit new to this so please do shout if I'm not making sense.

 

[Sektor]

Alternatively, for not to mess with Data Validation, this could be achieved with DropDowns. If that fits you, I can write the code.

 

[ChrisMac1]

Alternatively, for not to mess with Data Validation, this could be achieved with DropDowns. If that fits you, I can write the code.

Hi Sektor, and thanks for the help here. Ideally I just want to update the option in column AH to show EASY WIN. The Data Validation is built and then copied to other sheets so has to be there.

 

[rlv01]

What is the current data validation rule(s) for col AH?

 

[mumps]

Try:

Sub ChangeDV()
    Application.ScreenUpdating = False
    Dim v As Variant, v2 As Variant, i As Long
    v = Range("B2", Range("B" & Rows.Count).End(xlUp)).Resize(, 18).Value
    For i = LBound(v) To UBound(v)
        If v(i, 1) = "NO SHOW" And v(i, 18) < Date - 60 Then
            Range("AH" & i + 1) = "EASY WIN"
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[ChrisMac1]

Try:

Sub ChangeDV()
    Application.ScreenUpdating = False
    Dim v As Variant, v2 As Variant, i As Long
    v = Range("B2", Range("B" & Rows.Count).End(xlUp)).Resize(, 18).Value
    For i = LBound(v) To UBound(v)
        If v(i, 1) = "NO SHOW" And v(i, 18) < Date - 60 Then
            Range("AH" & i + 1) = "EASY WIN"
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Thanks mumps, standby, just testing it...