Results 1 to 3 of 3

code to install validation across a row

This is a discussion on code to install validation across a row within the Excel Questions forums, part of the Question Forums category; Hi all I have a table which is regularly sourced via "get data" from another xl file row A will ...

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Location
    melbourne australia
    Posts
    258

    Default

    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





  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default

    This should do.
    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


    tom

  3. #3
    Board Regular
    Join Date
    Jul 2002
    Location
    melbourne australia
    Posts
    258

    Default

    Tom,
    thank you, works perfect

    kd

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com