Trouble with dynamic named range

[vermutmb]

Hopefully there is a solution to my quandary this time. I am trying to
evaluate cells in a list to confirm that they are in a validation list.
Yes, I know that validation should have been used during entry, but it
was circumvented and I want to cycle through the cells and highlight
any that are not in the validation list.

My problem is that the following example code works when addressing a
range by cell reference (i.e. "c2:c9") and with a defined name range
that is limited (i.e. Shifts = "f3:f6"). But when I make the defined
name range dynamic, it stops working (i.e. Shifts =
offset(f3,0,0,counta(f3:f100),1) ). The cell loop goes across the
entire sheet, as it was from a more complicated example.

Could someone please explain what I am missing here?

Sub checkvalidation()
Dim LastRow As Long, LastCol As Long, rowstep As Long, colstep As Long,
validtype As Long
Dim validrng As Range

Application.EnableEvents = False
Application.ScreenUpdating = False

On Error GoTo errhandler

LastRow = Cells.SpecialCells(xlLastCell).Column
LastCol = Cells.SpecialCells(xlLastCell).Column

On Error Resume Next

For colstep = 1 To Last Col
For rowstep = 1 To LastRow
validtype = Cells(rowstep, colstep).Validation.Type
If validtype = 3 Then
Set validrng = Range("shifts")
If
IsError(Application.WorksheetFunction.Match(Cells(rowstep, colstep),
validrng, 0)) Then
With Cells(rowstep, colstep)
.Interior.ColorIndex = 45
End With
End If
End If
Next rowstep
Next colstep

errhandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub

End Sub

 

[Jim Thomlinson]

First things first. I think this line is incorrect...

LastRow = Cells.SpecialCells(xlLastCell).Column

you probably mean

LastRow = Cells.SpecialCells(xlLastCell).Row

 

[vermutmb]

Yeah, sorry about that, it was a quick cut and paste to replace a temp
number while I was trying to trap the problem. Still doesn't work.

marc

 

[vermutmb]

It appears that the problem is that, in order to address a dynamic
range, validrng needs to be set with a worksheet specified. So that,

Set validrng = worksheets("Sheet1").range("Shifts")

WILL work, but:

Set validrng = Range("Shifts")

will NOT.

Can someone explain this to me?

 

[Mark Driscol]

Be careful in the use of these names. If "Shifts" refers to cells
A1:A5 and then you use

Set validrng = worksheets("Sheet1").range("Shifts")

If "Shifts" then refers to cells A1:A10 later on, validrng will still
refer to cells A1:A5. You may already know that, but I thought I would
mention it in case you didn't.

Mark

 

[Marc]

Thanks, Mark. It's definitely a fine point I might not have realized,
but validrng would be changed through each loop and when the macro was
called, so that should be ok.