Hi,
I'm having a excel sheet that should search Column 'B' from cell value i put in 'G4',
everytime i put a value in cell G4.
Afterwards, if finding the value in Column 'B' (lets say example 'B14') it should put the cellvalue matched, in the same row, in column 'G' (example 'G14').
I have done that by this code (i'm a noob, so bare with me) :
Code
Private Sub CommandButton1_Click()
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
' forsæt ved fejl
'On Error Resume Next
Dim Cell As Range
Dim Checkrange As Range
Dim TValue As Variant
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$G$4" Then
'Ensure target is a number before multiplying by 2
'If IsNumeric(Target) Then
'Stop any possible runtime errors and halting code
' On Error Resume Next
'Turn off ALL events so the Target * 2 does not _
put the code into a loop.
' If Not Intersect(Target, Range("g4,i4")) Is Nothing Then
Range("G4").Select
Selection.copy
Application.EnableEvents = False
'Target = Target * 2
If Target.Value >= 1 Then
Call SoegOgMarker
End If
'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error GoTo 0
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
'End If
End Sub
Display More
This is the seeking and mark sub:
Code
Sub SoegOgMarker()
Dim LedEfter As String
Static Sidste As String
' Sidste = ActiveCell.Offset(0, -1).Address
LedEfter = Range("g4").Value
' Range(Sidste).Offset(1, 0).Activate
Cells.Find(what:=LedEfter, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 5).Activate
' Sidste = ActiveCell.Offset(0, 2).Address
End Sub
Display More
But my main problem is, can i somehow get this to work from 2 cells.
If any changes in G4 or I4, its should do the same search from either the cell value in G4 or if the change is in I4, it should do the search from that cell value, in Column B,
but if the cellchange is in I4, it should return the value in Column I (example I14, if found in B14)
Is that possible ?
Regards
Muggi