Change formating in other sheet cell based on match condition
 

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.

Change formating in other sheet cell based on match condition
 

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.

Change formating in other sheet cell based on match condition
 

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.

Change formating in other sheet cell based on match condition
 

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.

Change formating in other sheet cell based on match condition
 

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.

Change formating in other sheet cell based on match condition
 

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

Change formating in other sheet cell based on match condition
 

make sure that you have
OPTION EXPLICIT
at the top of the code module

DIM ws As WorkSheet
SET ws = Worksheets("sheet1")

now intellisense will work
when you type ws.




"Brandon" wrote:

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