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

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" <[email protected]> wrote in message
news:[email protected]...
|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

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" <[email protected]> wrote in
message
>news:[email protected]...
>|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
>
>
>.
>