Help with a formula that i cannot make work

[KEA1958]

Hi all, can anyonehelp with a formula. The purpose of this part of the spread sheet is toautomatically register the type of mailing done for later analysis -
J3 has a dropdown where you can choose numbers 1 to 5.(This is letter typereference). N3 also has a drop down with several texts 1. Open E R ... 2. TheOccupant.. 3.The Resident... 4.The Homeowner (This advises the addressee )P3& Q3 have '0' in them.
If I select 1-5 in J3 and 'Open E R' in N3 then I need '1' to appear in P3 andQ3 to remain '0'
If I select 1-5 in J3 and one of the other texts in N3 then I need P3 to remain'0' and Q3 to show '1'Basically as I work my way down the spread sheet I willhave a series of 1's and noughts in both P and Q columns with a total of 1's ineach column to simply add up and then percentile the difference between theamount of 'Open E R' (=Open Electoral Role) and the other options in 'N'
Finally, when a number is selected in 'J' and that automates the above I needauto recognition that that line in the spread sheet has been worked so I needthe line from column B to AA highlighted in a faint RED. When I tried to dothis in conditional formatting it filled some cells but not others ? I am on adeadline here and this issue has taken me to long so any help would very muchbe appreciated.<o></o>

 

[mumps]

Do the drop downs go down columns J and N or are they just in the two cells J3 and N3?

 

[KEA1958]

Do the drop downs go down columns J and N or are they just in the two cells J3 and N3?

Hi mumps, each cell in J and N right down to 35000 has the drop down in

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("N:N")) Is Nothing Then Exit Sub
    If Target = "Open E R" Then
        Target.Offset(0, 2) = "1"
        Target.Offset(0, 3) = "0"
    Else
        Target.Offset(0, 2) = "0"
        Target.Offset(0, 3) = "1"
    End If
    Range("B" & Target.Row & ":AA" & Target.Row).Interior.ColorIndex = 3
End Sub

The macro is automatically triggered by making a selection in the drop down in column N. So first make your selection in column J and last make your selection in column N.

 

[KEA1958]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("N:N")) Is Nothing Then Exit Sub
    If Target = "Open E R" Then
        Target.Offset(0, 2) = "1"
        Target.Offset(0, 3) = "0"
    Else
        Target.Offset(0, 2) = "0"
        Target.Offset(0, 3) = "1"
    End If
    Range("B" & Target.Row & ":AA" & Target.Row).Interior.ColorIndex = 3
End Sub

The macro is automatically triggered by making a selection in the drop down in column N. So first make your selection in column J and last make your selection in column N.

Hi mumps. Thank you very much. I will be back at work after the weekend when i will try . I cannot wait!
Kind Regards
Alan (UK)

 

[mumps]

Please let me know how it works out.

 

[KEA1958]

Please let me know how it works out.

Hi Mumps, to let you know that the macro worked on my system but worked intermittently oj nthe works system. Pretty 100% certain not your macro butthe rubbish systems we have at work. Have sorted now by using :

[FONT=&quot]For T3: =IF(ISBLANK(J3),"",IF(L3="Open E R",1,0))[/FONT][FONT=&quot]For U3: =IF(ISBLANK(J3),"",IF(L3="Open E R",0,1))

Provided by HansV at Answers MS Office

Thanks very much for all your help mumps.

Kindest regards

Alan (UK) [/FONT]

 

[mumps]

Glad it worked out.