[VBA] Look for an entry and update an adjacent cell

[Eawyne]

Hi all,

I want to have an inputbox to look for something in a sheet, and when I entered a value in the box, it will add the date to the adjacent cell after getting the focus on it. No need for any action on the cell, just looking for => Date.

I managed to find two codes that do parts of that request, but I'm stuck now, unable to merge them together to get the final result.

This one locates and moves to a cell when finding a value :

Sub Find_First()
    Dim FindString As String
    Dim Rng As Range
    FindString = InputBox("Enter a Search value")
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
                
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

This one does apply the Date to the cell containing the found value :

Sub Test()

Boite = Application.InputBox("Indiquez la boite à trouver :", "Boite")
   
Set found = Range("A:A").Find(What:=Boite, LookIn:=xlValues, LookAt:=xlWhole)

If found Is Nothing Then
    MsgBox "Cette boite n'existe pas", vbExclamation
    Else
   
    found.Offset(0, 1).Value = Date
 
        End If

End Sub

I tried to find exactly what allows to focus, but I just couldn't. Either articles are related to a specific question and thus hard to decypher, or it's too vague because I lack basics on coding...

What I'm looking for are values like this : R0125 ; I'd like to be able to "wildcard" the search, but again, I've been unable to find a specific item to help me out here.

Thanks for any help given !

 

[sykes]

As I said, I'd be inclined to use conditional formatting for your cell colouring...

Select B3:B200
Select "Conditional formatting"
Select "Use a formula to determine..."
Paste this into the formula field: =$B3<TODAY()-30
Select your format (pick off your desired yellow colour)
Select "OK"
Apply.

Now, your B3:B200 cells will turn the background yellow, if their dates are more than 30 days before today.
No code required.
Voila!!

A votre service.

 

[Eawyne]

Ohhh-kay. Weird, I did exactly search for conditional formatting, and that's what I found. I saw nothing about going into the formula field

 

[sykes]

... and has it worked for you?

 

[Eawyne]

... and has it worked for you?

Yep, it worked alright ! It had been years I hadn't used Excel in any proper way, so I wasn't even aware of the condtional formatting. It might come in handy more than once from now on.

 

[sykes]

Great! Glad we could help.

 

[Eawyne]

And thanks to you

 

[sykes]

Pleasure.