code to install validation across a row

[Kade]

Hi all
I have a table which is regularly sourced via "get data" from another xl file

row A will have "0" (zero) occur at irregular intervals

When "O" occurs I would like to automate a cell validation condition (="") on the next 5 cells in that same row (B,C,D,E,F)



I am stumped after
sub validatecells()

Application.Goto Reference:="Query_from_Excel_Files_1"

(pathetic isn't it)
any advice?

tia kd

 

[Legacy 98055]

This should do.
<pre>Sub AutoValidate()
Dim c As Range
For Each c In ActiveSheet.UsedRange.Columns("A").Cells
Debug.Print c.Address & ", " & c.Value
If Not IsEmpty(c) And c.Value = 0 Then
With Range(Cells(c.Row, 2), Cells(c.Row, 6)).Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="="""""
End With
End If
Next
End Sub

</pre>
tom

 

[Kade]

Tom,
thank you, works perfect

kd