Validate data
 

I have a workbook with 2 sheets. On sheet (1) is the
results of a macro. On sheet(2) is a set of valid values.

I would like to add an edit that compares the results in
Sheet(1) Col A with the valid values on sheet (2).

I was probably going to create a range for the valid
values but I'm looking for a easy way to cycle through
results in Col A sheet (1). I also want to highlight any
invalid value in Col A.

I appreciate any suggestions on how to accomplish this.
Thanks for the help

Validate data
 

You might try something like the following. Of course you'd have to adjust
how you set the two ranges. This assumes data starts at A1 in each sheet
and is contiguous.

Sub a()
Dim ValidRg As Range
Dim CheckRg As Range
Dim Cell As Range
With Workbooks("Book1").Worksheets("Sheet1") ''has valid entries
Set ValidRg = .Range("A1", .Range("A1").End(xlDown))
End With
With Workbooks("Book2").Worksheets("Sheet1") ''has values to check
Set CheckRg = .Range("A1", .Range("A1").End(xlDown))
End With
For Each Cell In CheckRg
If IsError(Application.Match(Cell.Value, ValidRg, False)) Then
Cell.Font.Bold = True
End If
Next
End Sub


--
Jim Rech
Excel MVP
"JT" wrote in message
...
|I have a workbook with 2 sheets. On sheet (1) is the
| results of a macro. On sheet(2) is a set of valid values.
|
| I would like to add an edit that compares the results in
| Sheet(1) Col A with the valid values on sheet (2).
|
| I was probably going to create a range for the valid
| values but I'm looking for a easy way to cycle through
| results in Col A sheet (1). I also want to highlight any
| invalid value in Col A.
|
| I appreciate any suggestions on how to accomplish this.
| Thanks for the help

Validate data
 

Thanks for the help.

-----Original Message-----
You might try something like the following. Of course
you'd have to adjust
how you set the two ranges. This assumes data starts at
A1 in each sheet
and is contiguous.

Sub a()
Dim ValidRg As Range
Dim CheckRg As Range
Dim Cell As Range
With Workbooks("Book1").Worksheets("Sheet1") ''has
valid entries
Set ValidRg = .Range("A1", .Range("A1").End
(xlDown))
End With
With Workbooks("Book2").Worksheets("Sheet1") ''has
values to check
Set CheckRg = .Range("A1", .Range("A1").End
(xlDown))
End With
For Each Cell In CheckRg
If IsError(Application.Match(Cell.Value, ValidRg,
False)) Then
Cell.Font.Bold = True
End If
Next
End Sub


--
Jim Rech
Excel MVP
"JT" wrote in
message
...
|I have a workbook with 2 sheets. On sheet (1) is the
| results of a macro. On sheet(2) is a set of valid
values.
|
| I would like to add an edit that compares the results in
| Sheet(1) Col A with the valid values on sheet (2).
|
| I was probably going to create a range for the valid
| values but I'm looking for a easy way to cycle through
| results in Col A sheet (1). I also want to highlight
any
| invalid value in Col A.
|
| I appreciate any suggestions on how to accomplish this.
| Thanks for the help


.