Scanning Through Filtered Data
 

Hi,

I need to be able to compare (manually) 2 separate
workbooks. The first one contains a series of updates that
are reflected in the second workbook. To make my life
easier, I was Auto Filtering the 2 workbooks so I could
focus on small portions at any single time.

What I would like to do is to be able to write a short
macro (invoked by pressing a character) that changes the
font color of the selected entry in the second workbook,
removing any highlighting from the selected entry in the
first workbook, change the font color of the selected entry
in the first workbook, then select the next entry in the
first workbook.

I have the following code (WKBOOK_NAME is a public const
whose value is the name of the first workbook):

Public Sub NextSelection()
Dim wkBook As Workbook
Dim r As Range
Dim r2 As Range

Set wkBook = ActiveWorkbook
Workbooks(WKBOOK_NAME).Activate

Set r = Selection
With r
.Font.ColorIndex = 10
.Interior.ColorIndex = xlNone
End With

Set r2 = r.Resize(2).Offset(1, 0).Resize(1)
r2.Select
r2.Interior.ColorIndex = 6
r2.Interior.Pattern = xlSolid

wkBook.Activate

Set r = Selection
r.Font.ColorIndex = 10

End Sub

The problem that I'm having is that the statement:
Set r2 = r.Resize(2).Offset(1, 0).Resize(1)
doesn't point me to the next entry in the filtered data,
but the next entry in the workbook (the entry filtered
entry could occur many rows later).

Any ideas on how to change this ??

Thanks for any help !!

Michael

Scanning Through Filtered Data
 

I was able to kludge this by checking to see if the row
that the entry is in is hidden.

If anyone has any other ideas though, please let me know.

Thanks.

Michael


-----Original Message-----
Hi,

I need to be able to compare (manually) 2 separate
workbooks. The first one contains a series of updates
that
are reflected in the second workbook. To make my life
easier, I was Auto Filtering the 2 workbooks so I could
focus on small portions at any single time.

What I would like to do is to be able to write a short
macro (invoked by pressing a character) that changes the
font color of the selected entry in the second workbook,
removing any highlighting from the selected entry in the
first workbook, change the font color of the selected
entry
in the first workbook, then select the next entry in the
first workbook.

I have the following code (WKBOOK_NAME is a public const
whose value is the name of the first workbook):

Public Sub NextSelection()
Dim wkBook As Workbook
Dim r As Range
Dim r2 As Range

Set wkBook = ActiveWorkbook
Workbooks(WKBOOK_NAME).Activate

Set r = Selection
With r
.Font.ColorIndex = 10
.Interior.ColorIndex = xlNone
End With

Set r2 = r.Resize(2).Offset(1, 0).Resize(1)
r2.Select
r2.Interior.ColorIndex = 6
r2.Interior.Pattern = xlSolid

wkBook.Activate

Set r = Selection
r.Font.ColorIndex = 10

End Sub

The problem that I'm having is that the statement:
Set r2 = r.Resize(2).Offset(1, 0).Resize(1)
doesn't point me to the next entry in the filtered data,
but the next entry in the workbook (the entry filtered
entry could occur many rows later).

Any ideas on how to change this ??

Thanks for any help !!

Michael
.