Newbie question

[EdL]

I'm new to vba programming and am basically looking to do the
following:

I am trying to keep track of football players for a fantasy football
draft. I have one sheet (master) with every football player listed
alphabetically (columns 1-2), a unique number (column 3) and position
they play (rb, wr, k, qb in column4).

In seperate worksheets named rb, wr, k, qb I have only those players
who play that position.

What I am looking to do is this: when I am on "master" sheet, I want
to be able to activate macro to change the font to red for the active
row, check column 4 for the position they play, activate the sheet
found in column 4, find the correct row (by matching my unique number
in column 3) and turn this row red.

I would appreciate any thoughts.

Thanks.

 

[Tom Ogilvy]

Sub TurnRed()
Dim sh As Worksheet
Dim rng As Range
Dim res As Variant
If LCase(ActiveSheet.Name) <> "master" Then Exit Sub
If ActiveCell.Column <> 1 Then Exit Sub
Cells.Font.ColorIndex = xlAutomatic
ActiveCell.EntireRow.Font.ColorIndex = 3
On Error Resume Next
Set sh = Worksheets(Cells(ActiveCell, 4))
On Error GoTo 0
If sh Is Nothing Then
MsgBox "Error, invalid sheet name"
Exit Sub
End If
With sh
Set rng = sh.Range(sh.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
res = Application.Match(ActiveCell, rng, 0)
If Not IsError(res) Then
sh.Cells.Font.ColorIndex = xlAutomatic
rng(res).EntireRow.Font.ColorIndex = 3
' Application.Goto rng, True
Else
MsgBox "Not found"
End If
End Sub

Put this in a general module and assign to a button from the forms toolbar.

 

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cRow As Long
Dim idx As Long
Dim sh As Worksheet

On Error GoTo ws_exit:
Cells.Font.ColorIndex = xlColorIndexAutomatic
Application.EnableEvents = False
With Target
If .Parent.Cells(.Row, "A").Value <> "" Then
idx = .Parent.Cells(.Row, "C").Value
.EntireRow.Font.ColorIndex = 3
Set sh = Worksheets(.Parent.Cells(.Row, "D").Value)
With sh
.Activate
On Error Resume Next
cRow = Application.Match( _
idx, .Range("A1:A1000"), 0)
On Error GoTo 0
If cRow > 0 Then
.Cells(cRow, 1).EntireRow.Font.ColorIndex = 3
End If
End With
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)