On Error Resume Next question

[night_writer]

My code is not acting as expected and I would appreciate some help in
understanding why. (I know very little about VBA programming, so
answers in the most basic terms would be helpful.)

My task is to go through each unlocked cell on a worksheet and set the
interior color based on the following criteria:

If there is data validation and the"Show error" box is checked, the
color should be light orange.
All other unlocked cells should be light yellow.

One of the problems I'm trying to work around is that if you test a
cell for Validation.ShowError and there is no data validation on the
cell, an error occurs. After reading other posts in this newsgroup, it
seems there is no simple way to test if data validation exists that
will generate a true/false answer, and the way around this is through
handling the error.

My code below attempts this. Every unlocked cell is turned yellow, and
then I tried to change only the ones with Validation.ShowError = True
to orange. Without the On Error Resume Next instruction, when the code
encounters a cell that is unlocked but does not have data validation,
I get an error at the second "if" clause. When I include the On Error
Resume Next statement, the code turns every cell unlocked cell to
orange, except for those where Validation.ShowError=False. (They stay
yellow.)

This is what I don't understand. Shouldn't the "next statement" after
the second If clause errors out be "End If"? It seems as if the line
that turns the color to orange is being treated like the next
statement, even though I think it's part of the If clause.

Any help or explanation would be appreciated.

The code starts here:
~~~~~~~~~~~~~~

Dim urRows As Integer, urCols As Integer
Dim i As Integer, j As Integer

Worksheets("Sheet1").Activate
ActiveSheet.UsedRange.Select

urRows = Selection.Rows.Count
urCols = Selection.Columns.Count

ActiveSheet.UsedRange.Cells(1, 1).Select

For i = 1 To urRows
For j = 1 To urCols
On Error Resume Next
ActiveSheet.UsedRange.Cells(i, j).Select

If ActiveSheet.UsedRange.Cells(i, j).Locked = False Then
ActiveSheet.UsedRange.Cells(i, j).Interior.Color =
RGB(255, 255, 153) ' color = yellow
If ActiveSheet.UsedRange.Cells(i, j).Validation.ShowError
= True Then _
ActiveSheet.UsedRange.Cells(i, j).Interior.Color =
RGB(255, 204, 153) ' color = orange
End If
Next j
Next i
~~~~~~~~~~~
Thanks
Alice

 

[Bob Phillips]

Dim urRows As Integer, urCols As Integer
Dim i As Integer, j As Integer
Dim dType As String

Worksheets("Sheet1").Activate

With ActiveSheet

urRows = .UsedRange.Rows.Count
urCols = .UsedRange.Columns.Count

For i = 1 To urRows
For j = 1 To urCols
With .UsedRange.Cells(i, j)

If .Locked = False Then
.Interior.Color = RGB(255, 255, 153) ' color =
yellow
dType = ""
On Error Resume Next
dType = .Validation.Type
On Error GoTo 0
If dType <> "" Then
If .Validation.ShowError = True Then _
.Interior.Color = RGB(255, 204, 153) ' color
= orange
End If
End If
End With
Next j
Next i
End With


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Trevor Shuttleworth]

Alice

the piece of code shown is missing an End If inside the For Loops. Probably
won't help ;-)

You should surround the code that can generate the error with On Error
Statements

On Error Resume Next ' switch on error trapping
' code that can produce the error
On Error Goto 0 ' switch off error trapping
' test for error condition

As it stands, the first time you go through the loop you switch on error
trapping and it stays on

Regards

Trevor

 

[night_writer]

Dim urRows As Integer, urCols As Integer
Dim i As Integer, j As Integer
Dim dType As String

Worksheets("Sheet1").Activate

With ActiveSheet

urRows = .UsedRange.Rows.Count
urCols = .UsedRange.Columns.Count

For i = 1 To urRows
For j = 1 To urCols
With .UsedRange.Cells(i, j)

If .Locked = False Then
.Interior.Color = RGB(255, 255, 153) ' color =
yellow
dType = ""
On Error Resume Next
dType = .Validation.Type
On Error GoTo 0
If dType <> "" Then
If .Validation.ShowError = True Then _
.Interior.Color = RGB(255, 204, 153) ' color
= orange
End If
End If
End With
Next j
Next i
End With

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Thank you. That does work! And also, for the very first time, I think
I understand how the "With" is supposed to work.

Your example is very concise and easy to follow.

Alice

 

[night_writer]

Alice

the piece of code shown is missing an End If inside the For Loops. Probably
won't help ;-)

You should surround the code that can generate the error with On Error
Statements

On Error Resume Next ' switch on error trapping
' code that can produce the error
On Error Goto 0 ' switch off error trapping
' test for error condition

As it stands, the first time you go through the loop you switch on error
trapping and it stays on

Regards

Trevor

Thanks, Trevor. I see that Bob Phillips example had the two error
traps as you stated, but I didn't know why. I often feel that VBA is
like a foreign language where I just don't know enough words. Things
almost make sense, and after a lot of labor, I can usually get close
to what I want, but the fine details still elude me!

Alice

 

[Steve Yandl]

Alice,

This doesn't show you anything about error capture but it is an alternate
approach to your task.

_________________________________

Sub test()
Dim rngValidation As Range
Dim rngTemp As Range

Set rngValidation =
ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

For Each rngTemp In ActiveSheet.UsedRange.Cells
If Not rngTemp.Locked Then
If Application.Intersect(rngTemp, rngValidation) Is Nothing Then
rngTemp.Interior.Color = RGB(255, 255, 153)
Else
If rngTemp.Validation.ShowError Then
rngTemp.Interior.Color = RGB(255, 204, 153)
Else
rngTemp.Interior.Color = RGB(255, 255, 153)
End If
End If
End If
Next rngTemp

End Sub
_________________________________

Steve

 

[Trevor Shuttleworth]

Hi Alice

Thanks for the feedback.

I guess VBA is one of those things where you just keep learning new tricks
all the time ;-)

With regard to Bob's code:

' set the variable dType to blank (a known state, not carried forward from
previous test(s))
dType = ""
' switch on Error Trapping - so that the code will continue to the next line
whatever happens
On Error Resume Next
' set dType to the Validation Type for the cell ... which will cause an
error if there isn't any
dType = .Validation.Type
' switch off Error Trapping ... and, in this case,is quite nicely the *next
line* for the Resume Next
On Error GoTo 0
' test the dType variable which will be blank if there was no Validation
(having pre-set it above)
If dType <> "" Then

Hope that makes it clearer.

You should always keep the error trapping range to a minimum as other errors
could be missed and, although it means your code won't fail, you may not get
the results you expect.

Regards

Trevor

 

[Dana DeLouis]

.Interior.Color = RGB(255, 255, 153)

Just throwing out an idea if you are using Xl 2007

If Not .Locked Then
.Interior.Color = XlRgbColor.rgbYellow

If .Validation.ShowError Then _
.Interior.Color = XlRgbColor.rgbOrange


Here's an alternative when working with Validation:

Sub Demo()
Dim RngValidation As Range
Dim Cell As Range
Const Orange = XlRgbColor.rgbOrange

Set RngValidation = Cells.SpecialCells(xlCellTypeAllValidation)
For Each Cell In RngValidation.Cells
If Cell.Validation.ShowError Then Cell.Interior.Color = Orange
Next Cell
End Sub

 

[my-wings]

Steve:

This is sweet!

I knew there had to be some way to "ask" Excel if a cell had data
validation, because I found the instructions about applying data validation
that say you need to "modify" a cell if data validation already exists,
otherwise you have to "add". It didn't make any sense that there was no way
to query a cell about it's data validation status. It looks like this
"SpecialCells" method provides a way.

And the "Intersection" method is something that looks very handy indeed. I'm
sure I will be using that a lot, now that my eyes have been opened.

Thanks so much for suggesting this different solution to the problem.

Alice
(aka night_writer)

 

[my-wings]

Dana:

Thanks for your additional suggestion. I see the handy "SpecialCells" method
shows up in your post as well as the one from Steve Yandl. This is going to
be very helpful for me on the current problem and going forward. Thanks so
much for your input!

Alice
(aka night_writer)

 

[Steve Yandl]

Alice,

You're welcome.

I have a hard time remembering to look at which types of cells can be
isolated using SpecialCells but it can be a very handy tool. The
'Intersection' method was something I ignored until the first time I decided
I had to use it and realized how many earlier tasks could have been made
easier if I'd researched a bit sooner.

There is a similar 'Union' method that allows you to combine ranges into a
bigger range. We could have cycled through the cells in the UsedRange and
used 'Union' to build a new range consisting of all the unlocked cells and
then used the Intersect method to find cells that were both unlocked and set
for data validation but I thought the nested If...Then...Else statements
might be easier to follow.

Steve