Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding specific text in one cell and returning data from another | Excel Worksheet Functions | |||
Send data from userform to specific cell on specific sheet? | Excel Programming | |||
Finding Number of cells in a column of other sheet having a specific word in them | New Users to Excel | |||
finding the address of a specific cell? | Excel Programming | |||
Finding specific data and moving it to a new sheet | Excel Programming |