Finding a specific cell value on different sheet

  • Thread starter Thread starter Looping through
  • Start date Start date
L

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
 
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
 
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
 
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.
 
"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
 
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
 
Thanks you to everyone who suggested something, I got my code to do exactly
what I want. You guys are awesome
 
Back
Top