New row with same conditional formating when previous row is complete

Pojky

New Member
Joined
Mar 1, 2011
Messages
4
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

RJPotts

Board Regular
Joined
Apr 11, 2007
Messages
139
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;

314w45y.jpg


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;

Code:
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

New Member
Joined
Apr 17, 2007
Messages
10
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

Board Regular
Joined
Apr 11, 2007
Messages
139
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

Board Regular
Joined
Apr 11, 2007
Messages
139

ADVERTISEMENT

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;

xc2ds4.jpg


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

New Member
Joined
Apr 17, 2007
Messages
10
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

Board Regular
Joined
Apr 11, 2007
Messages
139

ADVERTISEMENT

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

New Member
Joined
Apr 17, 2007
Messages
10
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

New Member
Joined
Apr 17, 2007
Messages
10
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top