Finding a specific cell value on different sheet
 

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

Finding a specific cell value on different sheet
 

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''s Student - gsnu200818

Finding a specific cell value on different sheet
 

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

--


Gary

"Looping through" wrote in message
...
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

Finding a specific cell value on different sheet
 

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.

"Gary''s Student" wrote:

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''s Student - gsnu200818

Finding a specific cell value on different sheet
 

"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

On Mon, 8 Dec 2008 11:09:01 -0800, Looping through
wrote:

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

Finding a specific cell value on different sheet
 

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
--
Gary''s Student - gsnu200818


"Looping through" wrote:

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.

"Gary''s Student" wrote:

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''s Student - gsnu200818

Finding a specific cell value on different sheet
 

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

"Gord Dibben" wrote:

"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

On Mon, 8 Dec 2008 11:09:01 -0800, Looping through
wrote:

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