Intermingled.


Option Explicit
Sub auto_open()

'define some variables
Dim myRng As Range
Dim myCell As Range

'what cells to look at. Change the worksheet name
'and the address to what you need
Set myRng = Worksheets("sheet1").Range("a1:A1000")

'look at each cell in that range
For Each myCell In myRng.Cells
With myCell
'offset(0,2) is two columns to the right
'since mycell is in column A, then .offset(0,2) is in column C
'remove any existing validation
.Offset(0, 2).Validation.Delete
If IsNumeric(.Value) _
And .Text Like "#.#" Then
'if it's a number that looks like 2.2 (or 3.6 or 7.9 or 8.0)
With .Offset(0, 2)
'clear out the value in Column C
.Value = ""
With .Validation
'add the Yes/No validation
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End With
else
'do nothing
'just leave column C's value alone.
End If
End With
Next myCell
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

stats wrote:

Hey Dave, can u please explain the code in brief..i m new to VBA...

--

Dave Peterson