How do you highlight a cell on Sheet 2 based on value in sheet 1.

[tottallyoff]

Here is my problem. This may seem trivial, but I am just starting out with VBA.

I have a spreadsheet with many worksheets in it. Sheet 'Names' has personal data for a person. Sheet 'Credits' has more information about that person's work.

Here is what I am trying to do:

When a cell in sheet 'Names' is clicked, the user is forwarded to cell containing the same name on sheet 'Credits'. I know how to do that with hyperlinks, but its not going to work becuase the list of names is updated.

What I am having problem with is:

1. How do I make the Sheet 'Names' react to clicking of the cell? Do I use procedure 'Worksheet_SelectionChange()'?

2. How do I switch sheets to highlight the cell on sheet 'Credits'?

3. How do I compare values in cells that are on 2 different sheets?

The code to highlight the cell is this, i think:




For x = 3 To <some number>
If Cells(x, 1).Value = <value of cell In sheet 'Names'> Then
Columns(x).EntireColumn.Activate
x=x+1000000
End If Next x


I know this may seem trivial, but I am very new to VBA.
Thank you.

[Charlize]

Maybe apply filter on sheet2 with name. Suppose sheet2 column A is name of person. You could use doubleclick on row of sheet1 were name of person is for example in Column A. In doubleclick event of sheet1 you store name of person of active row in a variable. This variable you use to filter on sheet2 so only rows with that name are visible.

or

A msgbox to show his credits. Loop through rows of sheet2 and if match add a line to the prompt of the msgbox until end of rows. If no match, prompt could be 'Nothing to show ...'.

Charlize

[Charlize]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim vname As String 'string to search for
Dim showinfo As String 'message to show with values found
Dim cell As Range 'the range where we want to look in
Dim norows As Long 'no of rows to look in
vname = Sheets(1).Range("A" & ActiveCell.Row).Value 'click anywhere in row for name
norows = Sheets(2).Range("A" & rows.Count).End(xlUp).Row 'determine no of rows in searcharea
'loop trough range
For Each cell In Sheets(2).Range("A1:A" & norows)
'if a match change message to show
If cell Like "*" & vname & "*" Then
showinfo = showinfo & vbCrLf & _
Sheets(2).Cells(cell.Row, 1).Value & " - " & _
Sheets(2).Cells(cell.Row, 2).Value
End If
Next cell
If showinfo = "" Then
MsgBox ("No info was found for " & vname)
Else
MsgBox ("Info for " & vname & vbCrLf & showinfo)
End If
'We don't want to edit the cell. You can use F2 instead.
Cancel = True
End Sub
Charlize

[tottallyoff]

Charlize.

Thank you for that script. I customize it to my workbook. This script
successfully links information from sheet 1 to sheet 2.

Now that the information is linked, how do I highlight the row on sheet 2 that I clicked on in sheet 1.

[Charlize]

First doubleclick in sheet 1 highlights with yellow. goto sheet2 and look.
Go back to sheet one and click again. Color is removed.
'rest of code is above. this is part to look for matches
For Each cell In Sheets(2).Range("A1:A" & norows)
'if a match change message to show
If cell Like "*" & vname & "*" Then
showinfo = showinfo & vbCrLf & _
Sheets(2).Cells(cell.Row, 1).Value & " - " & _
Sheets(2).Cells(cell.Row, 2).Value
'color the line if it isn't yellow
If Sheets(2).Range("A" & cell.Row & ":B" & cell.Row).Interior.ColorIndex <> 6 Then
Sheets(2).Range("A" & cell.Row & ":B" & cell.Row).Interior.ColorIndex = 6
Else
'if yellow, remove color
Sheets(2).Range("A" & cell.Row & ":B" & cell.Row).Interior.ColorIndex = xlColorIndexNone
End If
End If
Next cell
'rest of code in beneath this part
Charlize

[tottallyoff]

First doubleclick in sheet 1 highlights with yellow. goto sheet2 and look.
Go back to sheet one and click again. Color is removed.
Charlize

This is way too complex for what I am trying to do.

All I am trying to do is focus on the cell that matches the sheet 1 value.

[tottallyoff]

Sheets("Credits").Range("A" & cell.Row & ":B" & cell.Row).Select

[Charlize]

Now that the information is linked, how do I highlight the row on sheet 2 that I clicked on in sheet 1
Sorry, I thought you wanted color because you spoke about highlighting.

Charlize

[mdmackillop]

This assumes your Name is in Column A in Names.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
If Target <> "" Then
Set c = Sheets("Credit").Cells.Find(what:=Target.Value)
If Not c Is Nothing Then
Sheets("credit").Activate
c.Activate
Else
MsgBox Target & " not found."
End If
End If
End If
End Sub