I have defined a list in a table that contains a couple hundred names
(database). I have another sheet with a shorter list (entry). I would like to
be able to type names into that list and have the matching entry in the
database have it's formatting change. I'm able to get the corresponding
index, but I'm not sure how to change the formatting on another sheet. Here's
what I have so far on the entry sheet:

<SNIP
Private Sub Worksheet_Change(ByVal rangeTarget As Range)
If Intersect(rangeTarget, Range("listDraftPlayerNames")) Is Nothing Then
Exit Sub
If rangeTarget = 0 Then Exit Sub
' Declare some variables.
Dim dMatchIndex As Double
' Find the index to the list that matches the target exactly.
dMatchIndex = Application.WorksheetFunction.Match(rangeTarget,
Sheet3.Range("listRankyahooPlayerNames"), 0)
End Sub
</SNIP

If you haven't noticed, this is for a fantasy football draft. As the draft
progresses I plan on entering drafted players and I'd like them to disspear
to get crossed out on the databases so I know I cannot pick them.

Thanks.

matchindex retruns the row of the tabke looked at, and its 1 based (as
oppesed to zero based)
the row will be this index -1 as the offset

so, after
dMatchIndex = Application.WorksheetFunction ......

add this:

With rangeTarget.resize(1).Offset(dMatchindex-1)
..font.bold = True
..interior.color = vbYellow
End With





"Brandon" wrote:

I have defined a list in a table that contains a couple hundred names
(database). I have another sheet with a shorter list (entry). I would like to
be able to type names into that list and have the matching entry in the
database have it's formatting change. I'm able to get the corresponding
index, but I'm not sure how to change the formatting on another sheet. Here's
what I have so far on the entry sheet:

<SNIP
Private Sub Worksheet_Change(ByVal rangeTarget As Range)
If Intersect(rangeTarget, Range("listDraftPlayerNames")) Is Nothing Then
Exit Sub
If rangeTarget = 0 Then Exit Sub
' Declare some variables.
Dim dMatchIndex As Double
' Find the index to the list that matches the target exactly.
dMatchIndex = Application.WorksheetFunction.Match(rangeTarget,
Sheet3.Range("listRankyahooPlayerNames"), 0)
End Sub
</SNIP

If you haven't noticed, this is for a fantasy football draft. As the draft
progresses I plan on entering drafted players and I'd like them to disspear
to get crossed out on the databases so I know I cannot pick them.

Thanks.

I should explain
the table RESIZE(1) makes the table size one row, which is what we need ,
and then the OFFSET selects the row required. As its 1 based, the match would
return for example a 4 if the matched item was in the 4th row. Thus
OFFSET(dMatchIndex-1) moves the pointer from row 1 to row 1 + offest, ie 1+3
= 4

hope thsi is clear?


"Patrick Molloy" wrote:

matchindex retruns the row of the tabke looked at, and its 1 based (as
oppesed to zero based)
the row will be this index -1 as the offset

so, after
dMatchIndex = Application.WorksheetFunction ......

add this:

With rangeTarget.resize(1).Offset(dMatchindex-1)
.font.bold = True
.interior.color = vbYellow
End With





"Brandon" wrote:

I have defined a list in a table that contains a couple hundred names
(database). I have another sheet with a shorter list (entry). I would like to
be able to type names into that list and have the matching entry in the
database have it's formatting change. I'm able to get the corresponding
index, but I'm not sure how to change the formatting on another sheet. Here's
what I have so far on the entry sheet:

<SNIP
Private Sub Worksheet_Change(ByVal rangeTarget As Range)
If Intersect(rangeTarget, Range("listDraftPlayerNames")) Is Nothing Then
Exit Sub
If rangeTarget = 0 Then Exit Sub
' Declare some variables.
Dim dMatchIndex As Double
' Find the index to the list that matches the target exactly.
dMatchIndex = Application.WorksheetFunction.Match(rangeTarget,
Sheet3.Range("listRankyahooPlayerNames"), 0)
End Sub
</SNIP

If you haven't noticed, this is for a fantasy football draft. As the draft
progresses I plan on entering drafted players and I'd like them to disspear
to get crossed out on the databases so I know I cannot pick them.

Thanks.

The code you posted is changing the formatting on the sheet I'm calling
"entry". I would like to change the formatting on the other sheet "database".
How can I call those formatting attributes for another sheet?

Thanks,
-Brandon

"Patrick Molloy" wrote:

I should explain
the table RESIZE(1) makes the table size one row, which is what we need ,
and then the OFFSET selects the row required. As its 1 based, the match would
return for example a 4 if the matched item was in the 4th row. Thus
OFFSET(dMatchIndex-1) moves the pointer from row 1 to row 1 + offest, ie 1+3
= 4

hope thsi is clear?


"Patrick Molloy" wrote:

matchindex retruns the row of the tabke looked at, and its 1 based (as
oppesed to zero based)
the row will be this index -1 as the offset

so, after
dMatchIndex = Application.WorksheetFunction ......

add this:

With rangeTarget.resize(1).Offset(dMatchindex-1)
.font.bold = True
.interior.color = vbYellow
End With





"Brandon" wrote:

I have defined a list in a table that contains a couple hundred names
(database). I have another sheet with a shorter list (entry). I would like to
be able to type names into that list and have the matching entry in the
database have it's formatting change. I'm able to get the corresponding
index, but I'm not sure how to change the formatting on another sheet. Here's
what I have so far on the entry sheet:

<SNIP
Private Sub Worksheet_Change(ByVal rangeTarget As Range)
If Intersect(rangeTarget, Range("listDraftPlayerNames")) Is Nothing Then
Exit Sub
If rangeTarget = 0 Then Exit Sub
' Declare some variables.
Dim dMatchIndex As Double
' Find the index to the list that matches the target exactly.
dMatchIndex = Application.WorksheetFunction.Match(rangeTarget,
Sheet3.Range("listRankyahooPlayerNames"), 0)
End Sub
</SNIP

If you haven't noticed, this is for a fantasy football draft. As the draft
progresses I plan on entering drafted players and I'd like them to disspear
to get crossed out on the databases so I know I cannot pick them.

Thanks.

you can copy/pastespecial formats
thsi example copies the format from a range on sheet1 and format the same
size area to two other sheets...

Worksheets("sheet1").Range("B4:K17").Copy
Worksheets("Sheet2").Range("X1").PasteSpecial xlPasteFormats
Worksheets("Sheet3").Range("X1").PasteSpecial xlPasteFormats
Application.CutCopyMode = false



"Brandon" wrote:

The code you posted is changing the formatting on the sheet I'm calling
"entry". I would like to change the formatting on the other sheet "database".
How can I call those formatting attributes for another sheet?

Thanks,
-Brandon

"Patrick Molloy" wrote:

I should explain
the table RESIZE(1) makes the table size one row, which is what we need ,
and then the OFFSET selects the row required. As its 1 based, the match would
return for example a 4 if the matched item was in the 4th row. Thus
OFFSET(dMatchIndex-1) moves the pointer from row 1 to row 1 + offest, ie 1+3
= 4

hope thsi is clear?


"Patrick Molloy" wrote:

matchindex retruns the row of the tabke looked at, and its 1 based (as
oppesed to zero based)
the row will be this index -1 as the offset

so, after
dMatchIndex = Application.WorksheetFunction ......

add this:

With rangeTarget.resize(1).Offset(dMatchindex-1)
.font.bold = True
.interior.color = vbYellow
End With





"Brandon" wrote:

I have defined a list in a table that contains a couple hundred names
(database). I have another sheet with a shorter list (entry). I would like to
be able to type names into that list and have the matching entry in the
database have it's formatting change. I'm able to get the corresponding
index, but I'm not sure how to change the formatting on another sheet. Here's
what I have so far on the entry sheet:

<SNIP
Private Sub Worksheet_Change(ByVal rangeTarget As Range)
If Intersect(rangeTarget, Range("listDraftPlayerNames")) Is Nothing Then
Exit Sub
If rangeTarget = 0 Then Exit Sub
' Declare some variables.
Dim dMatchIndex As Double
' Find the index to the list that matches the target exactly.
dMatchIndex = Application.WorksheetFunction.Match(rangeTarget,
Sheet3.Range("listRankyahooPlayerNames"), 0)
End Sub
</SNIP

If you haven't noticed, this is for a fantasy football draft. As the draft
progresses I plan on entering drafted players and I'd like them to disspear
to get crossed out on the databases so I know I cannot pick them.

Thanks.

For some reason the Worksheets object call isn't working. When I manually
type it, the tooltip popup appears showing me the syntax, but it doesn't
appear to have any attributes, i.e. Range(...) when I've typed
Worksheets("Sheet1")

There clearly is a MS Excel Object called Sheet1 in the VB editor IDE... Argh.

I tried, but none seem to work:

Worksheets("Sheet1")
Worksheets(Sheet1)
Worksheets(1)


"Patrick Molloy" wrote:
you can copy/pastespecial formats
thsi example copies the format from a range on sheet1 and format the same
size area to two other sheets...

Worksheets("sheet1").Range("B4:K17").Copy
Worksheets("Sheet2").Range("X1").PasteSpecial xlPasteFormats
Worksheets("Sheet3").Range("X1").PasteSpecial xlPasteFormats
Application.CutCopyMode = false