Checking if Validation exists in the cell

[Lech Jaszowski]

I want to do some formatting for the cells with Validation.
For example in the enclosed code I begin to check cells from B9 down -
if Validation exists (I have Dropdown lists there), then I change interior
color.
But my method fails (generates error in the first cell without Validation)
and is not general.
Could you give me a hint?
Leszek

Sub change_cells_with_validation()
Dim for_change As Range
Set for_change = Range("B9")
'How to change following line to check if any Validation exists?
Do While for_change.Validation.InCellDropdown = True
for_change.Interior.ColorIndex = 35
Set for_change = for_change.Offset(1, 0)
Loop
End Sub

[[email protected]]

I think I'd put in an

On Error Resume Next


statement - although I'd also want to put in something to stop it when
it runs out of data!
Lech Jaszowski wrote:
> I want to do some formatting for the cells with Validation.
> For example in the enclosed code I begin to check cells from B9 down -
> if Validation exists (I have Dropdown lists there), then I change interior
> color.
> But my method fails (generates error in the first cell without Validation)
> and is not general.
> Could you give me a hint?
> Leszek
>
> Sub change_cells_with_validation()
> Dim for_change As Range
> Set for_change = Range("B9")
> 'How to change following line to check if any Validation exists?
> Do While for_change.Validation.InCellDropdown = True
> for_change.Interior.ColorIndex = 35
> Set for_change = for_change.Offset(1, 0)
> Loop
> End Sub

[Norman Jones]

Hi Lech,

Try something like:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim FirstCell As Range
Dim LastCell As Range

Set WB = Workbooks("YourBook.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

Set FirstCell = SH.Range("B9") '<<===== CHANGE
Set LastCell = SH.Cells(Rows.Count, FirstCell.Column).End(xlUp)

On Error Resume Next
Set rng = SH.Range(FirstCell, LastCell). _
SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

If Not rng Is Nothing Then rng.Interior.ColorIndex = 35

End Sub
'<<=============


---
Regards,
Norman


"Lech Jaszowski" <[email protected]> wrote in message
news:[email protected]...
>I want to do some formatting for the cells with Validation.
> For example in the enclosed code I begin to check cells from B9 down -
> if Validation exists (I have Dropdown lists there), then I change interior
> color.
> But my method fails (generates error in the first cell without Validation)
> and is not general.
> Could you give me a hint?
> Leszek
>
> Sub change_cells_with_validation()
> Dim for_change As Range
> Set for_change = Range("B9")
> 'How to change following line to check if any Validation exists?
> Do While for_change.Validation.InCellDropdown = True
> for_change.Interior.ColorIndex = 35
> Set for_change = for_change.Offset(1, 0)
> Loop
> End Sub
>
>