conditional formatting for newly added rows

1
votes

Ok, I really have no business working in spreadsheets or any data entry program for that matter. So basically what I am saying is I have no idea how to use much of the formula and script/language features. So any help would be AWESOME!

Heres my issue...

I have two Columns, One contains Conditional formatting (Cell Color) for for 3 variables respectfully. The Other Column is a DropDrown with a list.

I'm hoping to avoid explaining to the person(s) here how to keep applying rules as new Rows are added to the end of the sheet.

Is there an a script someone could dig up for me that (I'm guessing onEdit) will automatically apply my Conditional Formatting and DropDowns the new Rows?

Thank you so much!

1
google-sheetsgs-conditional-formatting
can you share an example of the sheet? – Blexy
I've been searching all weekend and cannot find a solution. I dont know much about scripting, how to write it and how it even works within the document... Any help you be great ; ) – user2970972
Blexy I missed your comment. I will post one up for you. – user2970972
Here is a link to a copy of the sheet. If B and G are =<> there are conditions as you will be able to see. I should have a dropdown with a list of shipping methods ie UPS, FedEx, overnight ect... The issue is, the clerks using this sheet... I don't want to have to tell them "when you get low on Rows and add more reapply the conditional formatting and dropdowns..." I just want this to automatically do this when new rows are added. Can you help? Let me know if you need more access to that sheet. Thanks so much! – user2970972
Could you give me edit permissions to the script? Also, could you paste your code in the comments into the question and format it? Hard to read. – Blexy

1 Answers

1
votes

I'm not sure this would ever receive another answer so, for the moment considering only I need to to just compare b3-g3>color h3 b4-g4> color h41:

In New Google Sheets, apply your formula to H2:- 

=arrayformula(if(G2:G=0,"",if(B2:B=G2:G,"Completed",IF(B2:B>G2:G,"Back Ordered",if(B2:B<G2:G,"Over Shipped")))))

and rules like these in Conditional formatting...:

SO19871471 example

and I think as new rows are added and data populated in A:G the formulae (both if and Conditional) extend automatically, with no fill where the cell in Column G is 0 or empty.