Detecting a paste of data that breaks the current color scheme and repairing color scheme

hi Ryan, nice to see you again here. Hope you and Aaron are doing well.

Option, please check attachment. The code is residing in "Small Example" sheet

Hi Watersev,

Thanks for your reply, I've found a few problems so far and they are:

1) When pasting in data it overwrites the formula column that MAY have values in. There is a working version in my copy of the code that only puts the formula in the blank cells.
2) It doesn't find the true last row but at the same time ignoring certain columns, in my version there is a working copy of the code that gets around this that you might be able to improve (Basically just needs to do it for any column with a formula in row 2)
3) When pasting in a big chunk of data the last row doesn't have formats. Can easily be fixed by changing the 5 to a 6 Range("a2:t2").Copy: Range("a3:t" & lrow + 6).PasteSpecial xlPasteFormats
4) The sheet is constantly trying to update its colors, formulas and border even when it doesn't need to, it should only be doing it if a big chunk of data is pasted messing up the color scheme or if the sheet has under 5 rows remaining.
5) With a large sheet of data the document becomes really laggy whenever you try and add just 1 more line because it's trying to fix the formats for loads of cells that already have the correct color scheme/formatting.

Cheers for your copy, it looks oddly simple but you're definitely understanding my aim. It's just has a few small bugs.

Thanks

hi there, please check attachment if it does everything you expect

Hey Watersev,

Thanks once again for your reply, it's much appreciated.

Unfortunately it's still overwriting any cells in the formula columns that may have values in them, values should not be overwritten by the formula.

I also encountered this problem: http://i.imgur.com/QzoR5AJ.png which happens when you manually add rows by just typing data in.

Is there an easier way to do the color formatting because all my sheets are different, I don't mind if it re-fixes the entire red box section as you can see: http://i.imgur.com/gG5O2EB.png and http://i.imgur.com/Cic92CW.png as it's not doing too much format fixing but it means the entire sheet range (A2:T2) can be done in 1 go rather than in batches.

Cheers once again, you're a very impressive coder :P

do you mean the formula should go in the blanks n:o only?

please check attachment, "Small Example" sheet code

please check attachment

Watersev,

You are a genius. It works perfectly!
There are a fair few very useful bits of code that I wasn't aware how to use but from your code I can now actually understand them.

Thank you ever so much!

Hey Watersev,

I just found another little bug that is a bit of a nuisance, I wonder if you could fix this one for me too.

If I paste a chunk of data into just one column it becomes a complete mess, checkout this example:

\1

hi Hyflex, please check attachment