Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    10

    Unanswered: Data validation depended on IF statement

    Hi folks,

    I want to apply data validation (simple yes/no), but only if the adjacent cell = 'no'. If the adjacent cell = 'yes', 'n/a' will apply.

    Is there a way to do this?

    Many thanks.

  2. #2
    Join Date
    May 2009
    Posts
    258
    I haven't tried this, but the following tutorial looks promising:
    Daily Dose of Excel Blog Archive Conditional Data Validation

    Regards,

    Ax

  3. #3
    Join Date
    Apr 2009
    Posts
    10
    Thanks. I'll check it out.

  4. #4
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    1

    Data Validation conditioned on IF Statement

    Create a macro.----If the data you want to match is in say column Q1.
    This code will change the matching cell to red, but you can use calcs or anything else based on the TRUE or FALSE return

    Range("Q1").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-2]<>""whatever data you want to match"",RC[-1]="" whatever data you want to match "")"
    Range("Q1").Select
    Selection.Copy
    Range("Q4:Q2000").Select
    ActiveSheet.Paste


    Range("Q:Q").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="TRUE"
    With Selection.FormatConditions(1).Font
    .Bold = True
    .Italic = True
    .ColorIndex = 1
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With

  5. #5
    Join Date
    Apr 2009
    Posts
    10
    Many thanks OldKritter, that looks like it might work.

Posting Permissions

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