Find data on different Sheet

  • Thread starter Looping through
  • Start date
L

Looping through

Does anyone have a macro that will find a information on a different sheet
and make it etire row bold?

If activecell b2 on sheet 2 is found in sheet 1, make entire row on sheet 1
bold, then delete entire row on sheet 2.

Thanks
 
I

Incidental

Hi LP

The code below is one way of doing what you are asking, with a button
on Sheet2 paste this code into the sheet2 code module, it will take
the value of the last cell selected and search for it in sheet1, if
found it will highlight the entire row in sheet1 and delete the entire
row in sheet2.

Option Explicit
Dim origCell As String
Dim findMe As String
Dim fndCell As Range
Private Sub CommandButton1_Click()

If findMe <> "" Then

With Sheets(1)

Set fndCell = .Cells.Find(What:=findMe, LookAt:=xlWhole)

If Not fndCell Is Nothing Then

fndCell.EntireRow.Font.Bold = True

Sheets(2).Range(origCell).EntireRow.Delete

End If

End With

End If

findMe = ""
origCell = ""

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count = 1 Then

findMe = Target.Value

origCell = Target.Address

End If

End Sub

I hope this helps you out

Steve
 
L

Looping through

Steve,

Thanks for your suggestion, but this is not working for me. I was hoping
this would be easy enough to insert into my existing string. This is what I
currently have and it works nicely if I start the code while Sheet 1 (Master
Log 2008) is active. But I would like this code to reconize if sheet 2
(Current Month Sales) is active to switch over to sheet 1, find the line
item selected in sheet 2 and continue.

Sub Lost_Quote()
' Macro recorded 5/19/2003 by Pete Wright

Dim rngToDelete As Range

Application.ScreenUpdating = False
ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select
With Selection.Font
.Name = "Arial"
.Size = 9
.Strikethrough = True
End With
ActiveCell.Offset(0, 11).Range("A1").Select
ActiveCell.FormulaR1C1 = "Lost"
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 9
.Strikethrough = False
End With

Set rngToDelete = Sheets("Current Month Sales").Cells.Find(What:=ActiveCell. _
Offset(0, -10).Value, LookIn:=xlFormulas, MatchCase:=False)
If rngToDelete Is Nothing Then
MsgBox "No Match"
Else
rngToDelete.EntireRow.Delete
End If
ActiveCell.Offset(0, -10).Select
End Sub

Thanks for your help.
 
I

Incidental

Hi LP

If your code is working fine and you just want to check the active
sheet you could add the If statement below into your macro, it will
check if the activesheet is "Current Month Sales" and if so it will
change it to "MasterLog 2008".

If ActiveSheet.Name = "Current Month Sales" Then

Sheets("MasterLog 2008").Activate

End If

i hope this helps

Steve
 
L

Looping through

I appreciate your help.

I want the user to activate a cell in sheet 2 (B25 as an example), based on
contents of the active cell, I want to goto sheet 1, find the matching data
of the active cell in sheet 2 and then continue the rest of my code.

Can that be done?
 
Top