Finding a specific cell value on different sheet

[Looping through]

Can that be done?

If the user selects and activates a cell in sheet 2 (C50 as an example), I
want to have a macro that will goto sheet 1, find the matching data within
cell C2-C1000 activate that cell and make it bold, then I want the macro to
come back to sheet 2 and change the original cell to Red.

Thanks

 

[Gary''s Student]

Sub looper()
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
ad = ActiveCell.Address
v = ActiveCell.Value
For i = 2 To 1000
If v = sh1.Cells(i, "C").Value Then
sh1.Cells(i, "C").Font.FontStyle = "Bold"
Exit For
End If
Next
sh2.Range(ad).Interior.ColorIndex = 3
End Sub

 

[Gary Keramidas]

you can try this:

Sub test()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim strToFind As String
Dim rngfound As Range
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("sheet2")
If ActiveSheet.Name = ws2.Name Then
strToFind = ActiveCell.Value

With ws.Range("C2:c1000")
Set rngfound = .Find(strToFind, lookat:=xlWhole,
LookIn:=xlValues)
End With

If Not rngfound Is Nothing Then
ws.Range(rngfound.Address).Font.Bold = True
ActiveCell.Font.ColorIndex = 3
End If
End If
End Sub

 

[Looping through]

Thanks Gary, this work's great.

How can I activate the cell in sheet 1 instead of just making it bold. I
just realized I need to insert some text in an adjascent cell.

I would like the entire row selected, offeset 11 cells over and insert the
word Lost.

 

[Gord Dibben]

"For example" means maybe more than one cell to be clicked on?

Can there be more than instance of the value on Sheet1?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range
Const WS_RANGE As String = "C1:C100" 'adjust to suit
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Range(WS_RANGE).Interior.ColorIndex = xlNone 'clear previous format
With Sheets("Sheet1").Range("C2:C100")
.Font.Bold = False 'clear previous bolded cells
Set c = .Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Font.Bold = True
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Target.Interior.ColorIndex = 3
End If
End Sub


Gord Dibben MS Excel MVP

 

[Gary''s Student]

This version will leave the found cell activated:

Sub looper()
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
ad = ActiveCell.Address
v = ActiveCell.Value
For i = 2 To 1000
If v = sh1.Cells(i, "C").Value Then
sh1.Cells(i, "C").Font.FontStyle = "Bold"
sh1.Activate
Cells(i, "C").Select
Exit For
End If
Next
sh2.Range(ad).Interior.ColorIndex = 3
End Sub

 

[Looping through]

Thanks you to everyone who suggested something, I got my code to do exactly
what I want. You guys are awesome