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

 

[ChrisMac1]

Thanks mumps, standby, just testing it...

Sorry mumps, in testing no entries values changed

 

[mumps]

I tested the macro on some dummy data based on the picture you posted and it worked properly. Unfortunately, most often a macro that works on sample data won't work on actual data. Could you upload your dummy doc from home?

 

[ChrisMac1]

I tested the macro on some dummy data based on the picture you posted and it worked properly. Unfortunately, most often a macro that works on sample data won't work on actual data. Could you upload your dummy doc from home?

I'll try to do that, good idea. I have to log off for the day now, so will get this done ASAP...

 

[mumps]

Click here to download the dummy. Run the macro in Module1.

 

[ChrisMcIntyre]

Here’s a link to the dummy doc Dropbox

 

[mumps]

The problem was that in your original picture, "NO SHOW" was all in upper case whereas your actual data is not. Try:

Sub ChangeDV()
    Application.ScreenUpdating = False
    Dim v As Variant, v2 As Variant, i As Long, DateValue As Date
    v = Range("B2", Range("B" & Rows.Count).End(xlUp)).Resize(, 18).Value
    For i = LBound(v) To UBound(v)
        DateValue = CDate(v(i, 18))
        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]

I did make updates there and was still unable to get this to work.

I have deployed a different solution now that seems to work. Adding a formula =IF(AND(B2=""No Show"",S2<TODAY()-60),""EASY WIN"") into column AI2 which is copied down the full rows of data, and that then copies the results back over the cells with data validation in, works a treat if not a bit longwinded.

mumps, thanks for everything, and apologies for any misdirection I caused.

 

[mumps]

You are very welcome and no apologies are necessary. The macro worked properly in the dummy you posted. However, I'm glad you found a different solution.