Need some help to automate a laborious process please

[Steven0605]

Hi all, I'm not sure if this is possible but I'm looking for some help to automate a process that I have to repeat week in week out on numerous spreadsheets. Let me explain how I currently do things:

Each week I get long spreadsheets full of data, these spreadsheets are always the same layout. I need to put separate if formulas into column S and column T, a calculation formula into column U and then a vlookup formula into columns V,W,X,Y. I then drag these formulas down to the bottom of the spreadsheet, circa 80k lines)

I then use conditional formatting to either highlight the rows in a different colour or to change the font colour in the row based on the results of the formulas I've used in columns S, T, U & V.

I guess what I'm asking is whether it is possible in any way to automate at least some of this rather repetitive process into a macro or a VBA button that could insert the formulas into the appropriate columns, and maybe even do the conditional formatting?

Many thanks in advance for any help.

 

[NoSparks]

Not knowing what the formulas are or what row you start them in I'd suggest you use the built in Macro Recorder and let Excel create a macro that you can study and learn from. Even post to the forum for assistance if need be.

 

[Fluff]

I can't help with the conditional formating, but as far as inserting formulae, have a look at this

Sub AddFormulae()

    Dim UsdRws As Long

    UsdRws = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Range("S2:S" & UsdRws).Formula = "=R2+M2"
    
    
End Sub