VBA - default contents of a cell based on numerical value of another

[jjjjjjjjunit]

Hi Guys,

I'm pretty new to VBA and am trying to make something work...

My code is below; it does not work.

This is probably because the syntax is wrong, but hopefully you can see what I'm trying to do...

I want the cell in column F to read "Cold" if the cell to the left of it (column E) has a date in it which is over 90 days old.

If its under 90 days, then i want it to do nothing as there is a data validation drop down box.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheets("Sheet1")
Dim lvalue As Date
lvalue = Now
If .Cells("E1").Value > lvalue - 90 Then
Else
If .Cells("E1").Value < lvalue - 90 Then .Cells("F1") = "COLD"
End If
End With
End Sub

[Richard Buttrey]

Hi,

Are you certain you actually need a macro? Where's the Data validation? Is that in F1 and other column F cells.

What event should trigger this macro? At the moment it will fire whenever any cell on the sheet is changed.
I'm guessing that when you pick a value from a dropdown in column F, then, and only then if the date is older than 90 days do you wish to change the data validation to Cold irrespective of what might have been selected in the drop down.

If so then use

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F:F")) Is Nothing Then
        If Target.Cells(, 0) < Date - 90 Then
            Application.EnableEvents = False
            Target = "Cold"
            Application.EnableEvents = True
        End If
    End If
End Sub

Otherwise upload the workbook.

[jjjjjjjjunit]

You've understood what I want, but I can't get it to work for some reason.

Not sure how to upload a workbook, so its here... https://www.dropbox.com/s/8qn87qlng8...safe.xlsm?dl=0

I have Data Validation in Column F and conditional formatting in column E.

If the date in "E" is over 90 days then it turns red, i want the word in "F" to also read "cold" at this point. doesn't have to be a macro if you have an easier way...

thank you by the way

[jjjjjjjjunit]

Password on the file is "Miami"

[jjjjjjjjunit]

Hi,

Are you certain you actually need a macro? Where's the Data validation? Is that in F1 and other column F cells.

What event should trigger this macro? At the moment it will fire whenever any cell on the sheet is changed.
I'm guessing that when you pick a value from a dropdown in column F, then, and only then if the date is older than 90 days do you wish to change the data validation to Cold irrespective of what might have been selected in the drop down.

If so then use

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F:F")) Is Nothing Then
        If Target.Cells(, 0) < Date - 90 Then
            Application.EnableEvents = False
            Target = "Cold"
            Application.EnableEvents = True
        End If
    End If
End Sub

Otherwise upload the workbook.

Hi Richard,

The above script gives me the desired outcome, but it is triggered in the wrong way. I need it to be automated, so that as soon as a date becomes older than 90 days, it triggers the even (change value of adjacent cell ("E") to "cold").

In its current format, it merely restricts the user entry options to "cold", but the user still has to actively attempt to change the value in that cell.

any suggestions? I can't seem to crack it.

[shirleyxls]

Try this in column F:

=IF(TODAY()-90>E29,"Cold","")

[jjjjjjjjunit]

problem with this, is that as soon as it is manually corrected to something else from the drop down box, then the formula will go. I need the formula to kick in again after another 90 days pass since editing.

[shirleyxls]

The formula solution won't work if you are still able to overwrite it with a selection from a drop down list, so it looks like Richard's vba suggestion is better.

He posted a couple of questions about how you want to trigger the change.

[jjjjjjjjunit]

Richards solution is what I was gunning for, though I put it into my worksheet and it didn't work; looking at it, it doesn't factor in column E at all (which is where the date is..

Richard: Dates are in column"E" and data validation is in column "F". I basically want each celling in "F" to default to "Cold" when its neighbouring cell in "E" contains a date that is over 90 days.

that make sensE?

[Richard Buttrey]

Richards solution is what I was gunning for, though I put it into my worksheet and it didn't work; looking at it, it doesn't factor in column E at all (which is where the date is..

Richard: Dates are in column"E" and data validation is in column "F". I basically want each celling in "F" to default to "Cold" when its neighbouring cell in "E" contains a date that is over 90 days.

that make sensE?

It does factor in column E although it's not immediately obvious. The macro fires when you make a change in column F. The code

If Target.Cells(, 0)

test to see if the cell relative to the target on the same row - that's the default "," in the parentheses, and in the column to the left, that's the "0" is 90 days ago or more. i.e. the cell one cell to the left of an F cell is an E cell.

[jjjjjjjjunit]

Hi Richard, Thankyou for the explanation... I'm on the same page now; the code works perfectly, and in response to your very first question, how can I change this so that it is triggered automatically when the date exceeds 90 days? Do I simply make the macro fire when the workbook is opened?

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[jjjjjjjjunit]

My apologies.. I was merely hoping to attract someones attention by stating that it was an easy solve for someone proficient in VBA. I haven't been able to change the title in the initial post, as there is no "Edit" button, but I have changed the most recent.

[zbor]

I've change it now for you.
People here are not attracted by complexity but by challenge. Proper title can narrow interested solvers much better.

[xlnitwit]

Hi,

I think the only real alteration required is to monitor column E rather than column F.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    
    On Error GoTo catch
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
            Application.EnableEvents = False
        For Each cell In Intersect(Target, Range("E:E")).Cells
            If IsDate(cell.Value) Then
                If cell.Value < Date - 90 Then cell.Offset(, 1).Value2 = "Cold"
            End If
        Next cell
    End If

catch:
    Application.EnableEvents = True
End Sub

[jjjjjjjjunit]

Hi,

I think the only real alteration required is to monitor column E rather than column F.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    
    On Error GoTo catch
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
            Application.EnableEvents = False
        For Each cell In Intersect(Target, Range("E:E")).Cells
            If IsDate(cell.Value) Then
                If cell.Value < Date - 90 Then cell.Offset(, 1).Value2 = "Cold"
            End If
        Next cell
    End If

catch:
    Application.EnableEvents = True
End Sub

thank you xlnitwit, however i think i may have made a mistake in the explanation.

Col "E" = a date

Col "F" = a word

if the date in "E" = over 90 days old, then "F" should = the word "cold"(unless is already reads "Cold")

[xlnitwit]

No, that was clear. The code monitors column E for changes in the date entered, and will subsequently update column F if necessary.

[jjjjjjjjunit]

I see.. I was testing it incorrectly...

This now works when I change the date. I suppose the only way to test if its automated is by setting some dates to 89 days and waiting until tomorrow?

Also, is there a way I can add a caveat to this? i.e. If "F" = "do not contact" the do nothing?

[xlnitwit]

You can certainly add a caveat. I fear I may have misjudged your requirement though. If you require the colouring to be 'live' so to speak, you will need to determine what should trigger the changes. I suspect the Workbook_Open event would also be necessary. To that end, add this code to a normal module:

Sub colourDates(DateRange As Range)
    Dim cell                  As Range
    On Error GoTo catch
    Application.EnableEvents = False
    For Each cell In DateRange.Cells

        If IsDate(cell.Value) Then
            If cell.Value < Date - 90 And LCase$(cell.Offset(, 1).Value) <> "do not contact" Then
                cell.Offset(, 1).Value2 = "Cold"
            End If
        End If
    Next cell

catch:
    Application.EnableEvents = True
End Sub

You may then alter the earlier Worksheet_Change code to simply this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        colourDates Intersect(Target, Range("E:E")).Cells
    End If
End Sub

and lastly change the Workbook_Open code to use the new routine as well:

Private Sub Workbook_Open()
    Dim lastRow               As Long
    Sheets("SalesLog").Visible = True
    Sheets("Orders").Visible = True
    Sheets("Reporting").Visible = True
    Sheets("Settings").Visible = xlHidden
    Sheets("DataBase").Visible = True
    Sheets("Trends").Visible = True
    Sheets("Compare").Visible = True
    Sheets("Trend Data").Visible = xlHidden
    Sheets("Client File").Visible = True

    Sheets("Reporting").Activate


    Sheets("Warning").Visible = xlVeryHidden
    With Sheets("Database")
        If .AutoFilterMode Then
            If .FilterMode Then .ShowAllData
        End If
        lastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
        colourDates .Range("E5:E" & lastRow)
    End With
End Sub