New row with same conditional formating when previous row is complete

[Pojky]

Basically i have a very basic work order, name, address, phone number spreadsheet to keep track of work (clients, jobs, dates, locations) . The are a few drop down menus. 1 drop down menu has 3 options and depending which option is chosen the row is highlighted a different color. What i would like is when i complete a certain cell, or press a button or however works, i want the next row to become active with all the same drop down menus and highlighting conditions. I would like this to happen everytime the previous row is complete.

After defining my data as a table i found that the conditional formatting was applied to the whole table.
rule: if n2="low"then(highlight a2:n2 yellow)
after defining as a table and adding 26 rows....
rule: if n2="low"then(highlight a2:n26 yellow)

so thats not working.
also i dont want to have to add rows. Idealy i want them to come up on their own for ease of use for other people.
any ideas welcome.
thanks in advance

 

[RJPotts]

Hi,

That sounds very similar to a problem I had in the past, that I needed a form which would expand if the end was reached but conditional formatting and data validation had to be carried over to the new row.

I havn't got access to the machine that form is on at the moment but fortunately found my original thread on here where Mohammad Basem had been kind enough to answer.

I've just created an example;

If I was to now type a name in cell b14 then as soon as I enter / tab from it a new line would be created with the no 14 in column a and both the data validation and conditional formatting applied in column c. If I had further conditional formatting / data validation in column d, e, f, etc that would also be copied down. The job types have data validation, only allowing 'Job Type 1', 'Job Type 2, 'Job Type 3,. If I understand your query correctly thats what you're looking for yes?

The code for the above is;

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A2").End(xlDown).Row = ActiveCell.Row Then
    Application.EnableEvents = False
    Rows(ActiveCell.Row).Select
    Selection.Copy
    Rows(ActiveCell.Row + 1).Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
    ActiveCell.ClearContents
    ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
    Application.EnableEvents = True
    Application.CutCopyMode = False
End If
End Sub

In the sheets I use this with when I use the last row I would start with the name column, the offsets & clear contents just moves down to the newly created row, removes the name from what had just been the last row (which was copied down with the conditional formatting & data validation) & then moves the active cell back up.

Hopefully that will have you up & running. If not please post a screenshot of the layout of your form so far.

Best regards
Richard

 

[kylezt]

Richard,

I have a similar situation, i have dependent pull downs in column F & G. And i have vlookup formulas in column H & I. All 4 columns have conditional formating and column A is numeric counter. i have a VBA code that resets the depended pull down when the primary pull down is changed. I would like to add new lines to my spread sheet as needed, but i have lines that start 3 from the last in the list for countif's and other formulas to give total status.

How do I add/insert lines with all the conditional formatting & data validation but also update the countif's and other formula's below the last line?

 

[RJPotts]

Richard,

I have a similar situation, i have dependent pull downs in column F & G. And i have vlookup formulas in column H & I. All 4 columns have conditional formating and column A is numeric counter. i have a VBA code that resets the depended pull down when the primary pull down is changed. I would like to add new lines to my spread sheet as needed, but i have lines that start 3 from the last in the list for countif's and other formulas to give total status.

How do I add/insert lines with all the conditional formatting & data validation but also update the countif's and other formula's below the last line?

Hi,

I'm in a bit of a rush (to leave work!) so cant create an example, I should be able to a little later this evening though so will reply properly then.

If I understand your query, you want a 'self expanding' table, underneath that 2 blank lines, then countifs etc which work on the entire column, even after they've expanded, yep? Thats no problem to do but easier to explain with an example, will be in touch later.

Best regards
Richard

 

[RJPotts]

Richard,

I have a similar situation, i have dependent pull downs in column F & G. And i have vlookup formulas in column H & I. All 4 columns have conditional formating and column A is numeric counter. i have a VBA code that resets the depended pull down when the primary pull down is changed. I would like to add new lines to my spread sheet as needed, but i have lines that start 3 from the last in the list for countif's and other formulas to give total status.

How do I add/insert lines with all the conditional formatting & data validation but also update the countif's and other formula's below the last line?

I've just created a quick demo;

Column B has data validation, only allowing Credit or Debit, column C is currency, column D is an IF formula which highlights debits = > £1,000. The formula in D2 is =IF(OR(B2="",C2=""),"",IF(AND(B2="Debit",C2>=1000),"Yes","No")), copied down to row 11.

C14 totals Debits on the sheet, formula is =SUMIF($B$2:$B12,"=Debit",$C$2:$C12)
C15 totals Credits on the sheet, formula is =SUMIF($B$2:$B12,"=Credit",$C$2:$C12)

C17 counts the number of transactions requiring approval (according to column D), formula is =COUNTIF($D$2:$D12,"Yes")
C18 sums the values of transactions requring approval (column C, according to column D), formula is =SUMIF($D$2:$D12,"=Yes",$C$2:$C12)

Note that any of the ranges in formulas include the blank line under line 10, i.e. they include row 12.

Now add in the vba shown above and give it a try.

It needs a bit of tidying up but that should at least get you started I think.

Best regards
Richard

 

[kylezt]

As i am not VBA savy, i currently have the following code in there (which when i changed from debit to credit resets my dependent pull down).
How do i add your code?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim rng As Range
Dim rngDV As Range

Set ws = ActiveSheet
On Error Resume Next
Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo errHandler

If Not Intersect(Target, rngDV) Is Nothing Then
If Target.Count > 1 Then Exit Sub
If Target.Column = 6 Then
Application.EnableEvents = False
Set rng = ActiveWorkbook.Names(Target.Value).RefersToRange
Target.Offset(0, 1).Value = rng.Offset(0, 0).Value
End If
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Could not change dependent cell"
GoTo exitHandler
End Sub

 

[RJPotts]

As i am not VBA savy, i currently have the following code in there (which when i changed from debit to credit resets my dependent pull down).
How do i add your code?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim rng As Range
Dim rngDV As Range

Set ws = ActiveSheet
On Error Resume Next
Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo errHandler

If Not Intersect(Target, rngDV) Is Nothing Then
If Target.Count > 1 Then Exit Sub
If Target.Column = 6 Then
Application.EnableEvents = False
Set rng = ActiveWorkbook.Names(Target.Value).RefersToRange
Target.Offset(0, 1).Value = rng.Offset(0, 0).Value
End If
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Could not change dependent cell"
GoTo exitHandler
End Sub

I think it would be a good idea to post a working sample of your current sheet.

Richard

 

[kylezt]

Not sure if this is what you are looking for, I tried the MrExcelHtml and whats below is what I got.
If you need for me to send you a copy let me know.

Excel 2007
A B C D E F G H I
2 Designer Lead Input Columns Designer Input Columns
3 ITEM # HARNESS NUMBER TITLE SYSTEM LOCATION Router Design Stage Router Status EV Layout Status EV Routing Status
4 1 70552-01152-042 RH JETT JETTISON CABIN ROUTING Completed ICS/ICD 100 90
5 2 70552-01153-042 LH JETT JETTISON CABIN ROUTING Completed ICS/ICD 100 90
6 3 70552-01192-041 CEFS PUMP CEFS TRANS ROUTING --Select-- 100 0
7 4 70552-02111-043 CARGO HOOK RLSE CARGO CABIN ROUTING Completed ICS/ICD 100 90
8 5 70552-02410-041 CARGO HOOK CARGO CABIN ROUTING Completed ICS/ICD 100 90
9 6 70552-06109-041 TAIL GB IVHMS IVHUMS TAILCONE ROUTING Completed ICS/ICD 100 90
10 7 70600-00261-052 DATABUS IVHMS CKPT ROUTING Completed ICS/ICD 100 90
11 8 70600-00261-058 DATABUS IVHMS CKPT ROUTING Completed ICS/ICD 100 90
12 9 70602-01144-085 CPLT O/B MFD J2 WEATHER RADAR/STAR SAFIRE CKPT LAYOUT IE Complete 100
13 10 70602-01144-086 PLT I/B MFD J2 WEATHER RADAR/STAR SAFIRE CKPT LAYOUT IE Complete 100
14
15
16
17 Layout 2 17.90%
18 Routing 8 11.30%
19
20 Ready for Flattening 7
21 Tools Ready 0
test

 

[kylezt]

I did a little research and trial and error. I created a macro with your code and assigned it to a button. I used the offset and clear contents command to clear column 2 thru 5 (one at a time), but i asked is there a way to select Not_Started in column 6 pull-down? Also is there a way to activate a range of column 2 thru 5 to clear? below is the code i have in the macro:

Sub Copy_1_Line
Application.EnableEvents = False
Rows(ActiveCell.Row).Select
Selection.Copy
Rows(ActiveCell.Row + 1).Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.ClearContents
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.ClearContents
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.ClearContents
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.ClearContents
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
Application.EnableEvents = True
Application.CutCopyMode = False
End Sub