Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using Vlookup to validate data | Excel Discussion (Misc queries) | |||
Data/Validate/List | Excel Worksheet Functions | |||
Validate Data Message Box | Excel Discussion (Misc queries) | |||
Validate cells have data | Excel Worksheet Functions | |||
validate data using code | Excel Programming |