Greetings,

I am using Excel from Office 2003 on an XP box.

I have several workbooks that use a drop down validation list to pick
name from an other workbook of names. It is working fine. The
problem is that the workbook of names called MCL.xls is being updated
due to errors in the list. When an entry in MCL.xls changes, the drop
down validation list no longer matches the value in the validation
drop down cell.

I need a way to identify these unmatched entries when I open each so
that I can manually find the new corrected entry from the drop down
list.

Anyone have any ideas?

Any help will be appreciated.

-Minitman

The code below uses the special cells method to find each validation list on
the worksheet. Then checks to see if the valkue in the cell matches the
validation list. The validatioin list has an equal sign in front of the
address range so I remove it wih the mid statement. Finally I use a FIND
method for checking if the data in he cell matches any value in the
validation list. If it doesn't a highlight the cell.

Sub test()

With Sheets("Sheet1")
Set Validatecells = .Cells.SpecialCells(Type:=xlCellTypeSameValidation )
For Each cell In Validatecells
'remove equal sign from formula
validationRange = Mid(cell.Validation.Formula1, 2)
Set c = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub


"Minitman" wrote:

Greetings,

I am using Excel from Office 2003 on an XP box.

I have several workbooks that use a drop down validation list to pick
name from an other workbook of names. It is working fine. The
problem is that the workbook of names called MCL.xls is being updated
due to errors in the list. When an entry in MCL.xls changes, the drop
down validation list no longer matches the value in the validation
drop down cell.

I need a way to identify these unmatched entries when I open each so
that I can manually find the new corrected entry from the drop down
list.

Anyone have any ideas?

Any help will be appreciated.

-Minitman

Hey Joel,

Thanks for the reply.

I could not get this code to work. I tried to follow your code but I
could not understand it.

It appears that I need to give a bit more information.

The column that has the validation is a named range called
rInputRefName.

The validation list has the formula: =RefName a dynamic named range
rerouted to the master customer list or MCL.xls.

What I need to do is check each cell in rInputRefName to see if there
is a match in the RefName list and if there is no exact match then
color that cell. If there is a match in RefName go to the next cell
and repeat the code.

Any other ideas?

-Minitman


On Fri, 17 Jul 2009 13:30:01 -0700, Joel
wrote:

The code below uses the special cells method to find each validation list on
the worksheet. Then checks to see if the valkue in the cell matches the
validation list. The validatioin list has an equal sign in front of the
address range so I remove it wih the mid statement. Finally I use a FIND
method for checking if the data in he cell matches any value in the
validation list. If it doesn't a highlight the cell.

Sub test()

With Sheets("Sheet1")
Set Validatecells = .Cells.SpecialCells(Type:=xlCellTypeSameValidation )
For Each cell In Validatecells
'remove equal sign from formula
validationRange = Mid(cell.Validation.Formula1, 2)
Set c = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub


"Minitman" wrote:

Greetings,

I am using Excel from Office 2003 on an XP box.

I have several workbooks that use a drop down validation list to pick
name from an other workbook of names. It is working fine. The
problem is that the workbook of names called MCL.xls is being updated
due to errors in the list. When an entry in MCL.xls changes, the drop
down validation list no longer matches the value in the validation
drop down cell.

I need a way to identify these unmatched entries when I open each so
that I can manually find the new corrected entry from the drop down
list.

Anyone have any ideas?

Any help will be appreciated.

-Minitman

Hey Joel,

Thanks for the reply.

I could not get this code to work. I tried to follow your code but I
could not understand it.

It appears that I need to give a bit more information.

The column that has the validation is a named range called
rInputRefName.

The validation list has the formula: =RefName a dynamic named range
rerouted to the master customer list or MCL.xls.

What I need to do is check each cell in rInputRefName to see if there
is a match in the RefName list and if there is no exact match then
color that cell. If there is a match in RefName go to the next cell
and repeat the code.

Any other ideas?

-Minitman


On Fri, 17 Jul 2009 13:30:01 -0700, Joel
wrote:

The code below uses the special cells method to find each validation list on
the worksheet. Then checks to see if the valkue in the cell matches the
validation list. The validatioin list has an equal sign in front of the
address range so I remove it wih the mid statement. Finally I use a FIND
method for checking if the data in he cell matches any value in the
validation list. If it doesn't a highlight the cell.

Sub test()

With Sheets("Sheet1")
Set Validatecells = .Cells.SpecialCells(Type:=xlCellTypeSameValidation )
For Each cell In Validatecells
'remove equal sign from formula
validationRange = Mid(cell.Validation.Formula1, 2)
Set c = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub


"Minitman" wrote:

Greetings,

I am using Excel from Office 2003 on an XP box.

I have several workbooks that use a drop down validation list to pick
name from an other workbook of names. It is working fine. The
problem is that the workbook of names called MCL.xls is being updated
due to errors in the list. When an entry in MCL.xls changes, the drop
down validation list no longer matches the value in the validation
drop down cell.

I need a way to identify these unmatched entries when I open each so
that I can manually find the new corrected entry from the drop down
list.

Anyone have any ideas?

Any help will be appreciated.

-Minitman

The code I provide should work under you case provide the following

1) You changed the sheet name to match the worksheet were the validation
cells are located.
2) The worksheet where the validation cells are located only has these
validation cells and no other.
3) The macro is located in the same workbook as the validation cells (not
MCL.xls )

What errors are you getting? ARe any cells being highlighted?

I reposted the code with comments

Sub test()

'sheet with validation cells
With Sheets("Sheet1")
'get all the validation cells
Set Validatecells = .Cells.SpecialCells(Type:=xlCellTypeSameValidation )
'check each validation cells
For Each cell In Validatecells
'remove equal sign from validation formula
validationRange = Mid(cell.Validation.Formula1, 2)
'compare data in the validation cell with the range of cells in MCL.xls
Set c = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
'if there is no match highlight the cell
If c Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub




"Minitman" wrote:

Hey Joel,

Thanks for the reply.

I could not get this code to work. I tried to follow your code but I
could not understand it.

It appears that I need to give a bit more information.

The column that has the validation is a named range called
rInputRefName.

The validation list has the formula: =RefName a dynamic named range
rerouted to the master customer list or MCL.xls.

What I need to do is check each cell in rInputRefName to see if there
is a match in the RefName list and if there is no exact match then
color that cell. If there is a match in RefName go to the next cell
and repeat the code.

Any other ideas?

-Minitman


On Fri, 17 Jul 2009 13:30:01 -0700, Joel
wrote:

The code below uses the special cells method to find each validation list on
the worksheet. Then checks to see if the valkue in the cell matches the
validation list. The validatioin list has an equal sign in front of the
address range so I remove it wih the mid statement. Finally I use a FIND
method for checking if the data in he cell matches any value in the
validation list. If it doesn't a highlight the cell.

Sub test()

With Sheets("Sheet1")
Set Validatecells = .Cells.SpecialCells(Type:=xlCellTypeSameValidation )
For Each cell In Validatecells
'remove equal sign from formula
validationRange = Mid(cell.Validation.Formula1, 2)
Set c = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub


"Minitman" wrote:

Greetings,

I am using Excel from Office 2003 on an XP box.

I have several workbooks that use a drop down validation list to pick
name from an other workbook of names. It is working fine. The
problem is that the workbook of names called MCL.xls is being updated
due to errors in the list. When an entry in MCL.xls changes, the drop
down validation list no longer matches the value in the validation
drop down cell.

I need a way to identify these unmatched entries when I open each so
that I can manually find the new corrected entry from the drop down
list.

Anyone have any ideas?

Any help will be appreciated.

-Minitman

Hey Joel,

Sorry about the duplicate reply.

The first thing I did was to dim the variables including "cell".

I set up a CommandButton to run your code.

I changed the sheet name to the actual sheet name.

This is what I came up with. Do you see any errors (other word wrap
problems)?
_____________________________________
Private Sub CommandButton1_Click()
Test
End Sub

Sub Test()
Dim Validatecells
Dim validationRange
Dim C
Dim cell

'sheet with validation cells
With Sheets("Input")
'get all the validation cells
Set Validatecells = _
.Cells.SpecialCells(Type:=xlCellTypeSameValidation )
'check each validation cells
For Each cell In Validatecells
'remove equal sign from validation formula
validationRange = Mid(cell.Validation.Formula1, 2)
'compare data in the validation cell with the range of cells in
'MCL.xls
Set C = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
'if there is no match highlight the cell
If C Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub
_____________________________________

I get this error:
_____________________________________

Run-time error '1004':
No cells were found.
_____________________________________

Then debug high lights this line:

Set Validatecells = _
.Cells.SpecialCells(Type:=xlCellTypeSameValidation )

Any ideas?

-Minitman



On Fri, 17 Jul 2009 22:55:01 -0700, Joel
wrote:

The code I provide should work under you case provide the following

1) You changed the sheet name to match the worksheet were the validation
cells are located.
2) The worksheet where the validation cells are located only has these
validation cells and no other.
3) The macro is located in the same workbook as the validation cells (not
MCL.xls )

What errors are you getting? ARe any cells being highlighted?

I reposted the code with comments

Sub test()

'sheet with validation cells
With Sheets("Sheet1")
'get all the validation cells
Set Validatecells = .Cells.SpecialCells(Type:=xlCellTypeSameValidation )
'check each validation cells
For Each cell In Validatecells
'remove equal sign from validation formula
validationRange = Mid(cell.Validation.Formula1, 2)
'compare data in the validation cell with the range of cells in MCL.xls
Set c = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
'if there is no match highlight the cell
If c Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub




"Minitman" wrote:

Hey Joel,

Thanks for the reply.

I could not get this code to work. I tried to follow your code but I
could not understand it.

It appears that I need to give a bit more information.

The column that has the validation is a named range called
rInputRefName.

The validation list has the formula: =RefName a dynamic named range
rerouted to the master customer list or MCL.xls.

What I need to do is check each cell in rInputRefName to see if there
is a match in the RefName list and if there is no exact match then
color that cell. If there is a match in RefName go to the next cell
and repeat the code.

Any other ideas?

-Minitman


On Fri, 17 Jul 2009 13:30:01 -0700, Joel
wrote:

The code below uses the special cells method to find each validation list on
the worksheet. Then checks to see if the valkue in the cell matches the
validation list. The validatioin list has an equal sign in front of the
address range so I remove it wih the mid statement. Finally I use a FIND
method for checking if the data in he cell matches any value in the
validation list. If it doesn't a highlight the cell.

Sub test()

With Sheets("Sheet1")
Set Validatecells = .Cells.SpecialCells(Type:=xlCellTypeSameValidation )
For Each cell In Validatecells
'remove equal sign from formula
validationRange = Mid(cell.Validation.Formula1, 2)
Set c = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub


"Minitman" wrote:

Greetings,

I am using Excel from Office 2003 on an XP box.

I have several workbooks that use a drop down validation list to pick
name from an other workbook of names. It is working fine. The
problem is that the workbook of names called MCL.xls is being updated
due to errors in the list. When an entry in MCL.xls changes, the drop
down validation list no longer matches the value in the validation
drop down cell.

I need a way to identify these unmatched entries when I open each so
that I can manually find the new corrected entry from the drop down
list.

Anyone have any ideas?

Any help will be appreciated.

-Minitman

The input sheet doesn't have a cells with validation lists. Do you have more
htan one workbook that your working with? Is Input sheet in the same
workbook as the macro? Is the macro in a module VBA sheet?

"Minitman" wrote:

Hey Joel,

Sorry about the duplicate reply.

The first thing I did was to dim the variables including "cell".

I set up a CommandButton to run your code.

I changed the sheet name to the actual sheet name.

This is what I came up with. Do you see any errors (other word wrap
problems)?
_____________________________________
Private Sub CommandButton1_Click()
Test
End Sub

Sub Test()
Dim Validatecells
Dim validationRange
Dim C
Dim cell

'sheet with validation cells
With Sheets("Input")
'get all the validation cells
Set Validatecells = _
.Cells.SpecialCells(Type:=xlCellTypeSameValidation )
'check each validation cells
For Each cell In Validatecells
'remove equal sign from validation formula
validationRange = Mid(cell.Validation.Formula1, 2)
'compare data in the validation cell with the range of cells in
'MCL.xls
Set C = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
'if there is no match highlight the cell
If C Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub
_____________________________________

I get this error:
_____________________________________

Run-time error '1004':
No cells were found.
_____________________________________

Then debug high lights this line:

Set Validatecells = _
.Cells.SpecialCells(Type:=xlCellTypeSameValidation )

Any ideas?

-Minitman



On Fri, 17 Jul 2009 22:55:01 -0700, Joel
wrote:

The code I provide should work under you case provide the following

1) You changed the sheet name to match the worksheet were the validation
cells are located.
2) The worksheet where the validation cells are located only has these
validation cells and no other.
3) The macro is located in the same workbook as the validation cells (not
MCL.xls )

What errors are you getting? ARe any cells being highlighted?

I reposted the code with comments

Sub test()

'sheet with validation cells
With Sheets("Sheet1")
'get all the validation cells
Set Validatecells = .Cells.SpecialCells(Type:=xlCellTypeSameValidation )
'check each validation cells
For Each cell In Validatecells
'remove equal sign from validation formula
validationRange = Mid(cell.Validation.Formula1, 2)
'compare data in the validation cell with the range of cells in MCL.xls
Set c = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
'if there is no match highlight the cell
If c Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub




"Minitman" wrote:

Hey Joel,

Thanks for the reply.

I could not get this code to work. I tried to follow your code but I
could not understand it.

It appears that I need to give a bit more information.

The column that has the validation is a named range called
rInputRefName.

The validation list has the formula: =RefName a dynamic named range
rerouted to the master customer list or MCL.xls.

What I need to do is check each cell in rInputRefName to see if there
is a match in the RefName list and if there is no exact match then
color that cell. If there is a match in RefName go to the next cell
and repeat the code.

Any other ideas?

-Minitman


On Fri, 17 Jul 2009 13:30:01 -0700, Joel
wrote:

The code below uses the special cells method to find each validation list on
the worksheet. Then checks to see if the valkue in the cell matches the
validation list. The validatioin list has an equal sign in front of the
address range so I remove it wih the mid statement. Finally I use a FIND
method for checking if the data in he cell matches any value in the
validation list. If it doesn't a highlight the cell.

Sub test()

With Sheets("Sheet1")
Set Validatecells = .Cells.SpecialCells(Type:=xlCellTypeSameValidation )
For Each cell In Validatecells
'remove equal sign from formula
validationRange = Mid(cell.Validation.Formula1, 2)
Set c = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub


"Minitman" wrote:

Greetings,

I am using Excel from Office 2003 on an XP box.

I have several workbooks that use a drop down validation list to pick
name from an other workbook of names. It is working fine. The
problem is that the workbook of names called MCL.xls is being updated
due to errors in the list. When an entry in MCL.xls changes, the drop
down validation list no longer matches the value in the validation
drop down cell.

I need a way to identify these unmatched entries when I open each so
that I can manually find the new corrected entry from the drop down
list.

Anyone have any ideas?

Any help will be appreciated.

-Minitman