View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
Steve Yandl Steve Yandl is offline
external usenet poster
 
Posts: 284
Default On Error Resume Next question

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(xlCellTypeAllVa lidation)

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



wrote in message
ups.com...
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 he
~~~~~~~~~~~~~~

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