Thanks Bob. I pasted this into the worksheet's module but the formula does
not change when I make a selection. Have I missed something?


Thanks.


"Bob Phillips" wrote:

Okay, give this a whirl

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B33"
Dim iCtr As Long
Dim sNo As String
Dim sYes As String
Dim LastRow As Long

On Error GoTo ws_exit
Application.EnableEvents = False

LastRow = 0

Call EndRange(LastRow)

If Range("B33").Value = "Yes" Then
For iCtr = 36 To LastRow
sYes = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10"
& _
"=""Yes"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _
"C16,IF(ISBLANK(R6C2),(R" & iCtr & "C11* R" & iCtr & _
"C7),(R" & iCtr & "C11* R" & iCtr & "C7)/365*(R6C2)))))"
Range("L" & iCtr).FormulaR1C1 = sYes
Next
ElseIf Range("B33").Value = "No" Then
For iCtr = 36 To LastRow
sNo = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" &
_
"=""No"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _
"C16,IF(ISBLANK(R6C2),(R" & iCtr & "C11* R" & iCtr & _
"C9),(R" & iCtr & "C11* R" & iCtr & "C9)/365*(R6C2)))))"
Range("L" & iCtr).FormulaR1C1 = sNo
Next
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karen53" wrote in message
...
Yes, the yes/no DV is in B33 only. rows 36 down is where the formulas go.
No
DV there

Thanks


"Bob Phillips" wrote:

You mean that you want this macro to run when you make a selection in a
Data
Validation with just Yes/No in cell B33,?

Or is it DV in B36 down?
--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)