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
<(E-Mail Removed)> 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
<(E-Mail Removed)> 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
> <(E-Mail Removed)> 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
<(E-Mail Removed)> 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
>> <(E-Mail Removed)> 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
> <(E-Mail Removed)> 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
> >> <(E-Mail Removed)> 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
> >> >>
> >>
> >>
>
>

There are two workbooks - MCL.xls and 2004-05.xls. MCL has the list
of names and 2004-05 has the column with the validation cells (most of
which are empty).

The macro is in the code section of the worksheet. I am using a
CommandButton to trigger the code.

-Minitman



On Sat, 18 Jul 2009 22:18:01 -0700, Joel
<(E-Mail Removed)> wrote:

>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
>> <(E-Mail Removed)> 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
>> >> <(E-Mail Removed)> 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 should be in the workbook 2004-2005.xls on either a module sheet or
the Input VBA sheet.

"Minitman" wrote:

> There are two workbooks - MCL.xls and 2004-05.xls. MCL has the list
> of names and 2004-05 has the column with the validation cells (most of
> which are empty).
>
> The macro is in the code section of the worksheet. I am using a
> CommandButton to trigger the code.
>
> -Minitman
>
>
>
> On Sat, 18 Jul 2009 22:18:01 -0700, Joel
> <(E-Mail Removed)> wrote:
>
> >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
> >> <(E-Mail Removed)> 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
> >> >> <(E-Mail Removed)> 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
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>
>

I didn't finish my message

I just ran the code below on a new workbook with a sheet called Input.
First I didn't have any validation list and got the error 1004. Then I added
a validaion list and didn't get the error. This indicates the code was
exeercising the workbook with worksheet Input otherwise you would get an
error on the WITH statement. Do you have any On Error statments? Comment
them out it may be masking a different error.

sub test()
With Sheets("Input")
Set Validatecells = _
.Cells.SpecialCells(Type:=xlCellTypeSameValidation)
End With
End Sub


"Minitman" wrote:

> There are two workbooks - MCL.xls and 2004-05.xls. MCL has the list
> of names and 2004-05 has the column with the validation cells (most of
> which are empty).
>
> The macro is in the code section of the worksheet. I am using a
> CommandButton to trigger the code.
>
> -Minitman
>
>
>
> On Sat, 18 Jul 2009 22:18:01 -0700, Joel
> <(E-Mail Removed)> wrote:
>
> >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
> >> <(E-Mail Removed)> 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
> >> >> <(E-Mail Removed)> 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
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>
>