Automatic Formatting

[richphi37]

Team,

I receive a report each week which is formatted terribly. I spend about 10 minutes getting everything the way I like it before I can start to look at the data and see what needs to be done. How can I get it to auto-format if I paste it into my worksheet. Attached are pictures of the two sheets in the workbook. One sheet is labeled: Weekly Report, this is how I get it, and the second is labeled "How I set it Up." I want it to automatically go from Weekly report to How I set it up. some columns are moved, some are erased, and some have conditional formatting. Project has "Green" "Blue" and "red" highlighted, and Project End date has every date in the past highlighted.

This is a drastically scaled down representation.

I'm guessing its a VBA solution, but I only know so far as simple macros.

Thanks for you help

-Phil

 

[Wookiee]

I'm not sure where the Project End Date data came from since I didn't see it depicted in the Weekly Report image, but I put together a macro to clean up data based on your criteria. Don't think it's the final product you'll need, but it should get you pretty close.

Sub RoughPrep()

Dim lngLastRow As Long

lngLastRow = Range("A" & Rows.Count).End(xlUp).Row

'Delete Superfluous Columns
Range("B:B,H:H").Delete Shift:=xlToLeft

'Move Project Column
Columns("C:C").Cut
Columns("E:E").Insert Shift:=xlToRight
Application.CutCopyMode = False

'Add Continuous Cell Borders
ActiveSheet.UsedRange.Borders.LineStyle = xlContinuous

'Color Header Row
Range("A1:F1").Interior.Color = 15773696
'Update "F" to whichever column is the last to contain data

'Add Conditional Formatting For Projects
Range("D2:D" & lngLastRow).Select

With Selection

   .FormatConditions.Add _
      Type:=xlExpression, _
      Formula1:="=OR($D2=""red"",$D2=""green"",$D2=""blue"")"
   .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
   .FormatConditions(1).Interior.Color = 255
   .FormatConditions(1).StopIfTrue = False
   
End With

'Add Conditional Formatting For Project End Dates
Range("G2:G" & lngLastRow).Select

With Selection

   .FormatConditions.Add _
      Type:=xlExpression, _
      Formula1:="=$G2<TODAY()"
   .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
   .FormatConditions(1).Interior.Color = 255
   .FormatConditions(1).StopIfTrue = False
   
End With
    
Range("A1").Select

End Sub

 

[richphi37]

Wookie,

Thanks for the help. Im brand new to VBS. Dont really know anything, so I want to be clear. The green writing you put in are notes for me, not the code right? and when I input it in should I leave the spacings between each or should they all be jammed together. Do the colors have particular meanings? Is it typically a space between the functions? I appreciate your help as I learn all this.

 

[Wookiee]

Wookie,

Thanks for the help. Im brand new to VBS. Dont really know anything, so I want to be clear. The green writing you put in are notes for me, not the code right? and when I input it in should I leave the spacings between each or should they all be jammed together. Do the colors have particular meanings? Is it typically a space between the functions? I appreciate your help as I learn all this.

I'm glad I could help. To answer your questions:

The colors do mean different things, and text shown in green font indicates a comment (so VBA doesn't evaluate or act on anything preceded by an apostrophe).
The spaces between lines are not required; I structure my code that way for better legibility.
I would encourage you to copy the entire code into your module and then edit it from there. Be sure to test it out on a copy of your source file.

 

[richphi37]

That's great it totally works!! Thanks so much. to get the project end date, its =edate(F2,120
I tried looking it up online, but could not find how to write a function. I find that frustrating.
Could you tell me how to write this last bit. I appreciate all your help.

and does it matter where I insert it in the code?

 

[Wookiee]

I've tweaked the macro to include your EDATE formula. I also added a few extras like freezing the top row, adding an autofilter, and expanding the columns to accommodate the data.

The easiest way to get the code for a formula you've already got in a worksheet is to activate the Macro Recorder. Next click in the cell with the formula you want to use. Hit F2 then ENTER. This will force the recorder to record the formula in VBA. Stop the recorder then go into the VB Editor and you'll have your formula.

As far as where you insert that formula in the code, it doesn't really matter as long as it's after the code which deletes columns.

Sub RoughPrep2()
'Crafted 16 Jul 2020 by Wookiee at MrExcel.com
'https://www.mrexcel.com/board/threads/automatic-formatting.1140054/#post-5522714

'Declare Variable
Dim lngLastRow As Long

'Determine Last Row With Data
lngLastRow = Range("A" & Rows.Count).End(xlUp).Row

'Delete Superfluous Columns
Range("B:B,H:H").Delete Shift:=xlToLeft

'Move Project Column
Columns("C:C").Cut
Columns("E:E").Insert Shift:=xlToRight
Application.CutCopyMode = False

'Add Continuous Cell Borders
ActiveWindow.DisplayGridlines = False
ActiveSheet.UsedRange.Borders.LineStyle = xlContinuous

'Color Header Row
Range("A1:G1").Interior.Color = 15773696
'Update "G" to whichever column is the last to contain data

'Add Conditional Formatting For Projects
Range("D2:D" & lngLastRow).Select

With Selection

   .FormatConditions.Add _
      Type:=xlExpression, _
      Formula1:="=OR($D2=""red"",$D2=""green"",$D2=""blue"")"
   .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
   .FormatConditions(1).Interior.Color = 255
   .FormatConditions(1).StopIfTrue = False
   
End With

'Format Date Columns
Range("F2:G" & lngLastRow).NumberFormat = "m/d/yyyy"

'Insert Project End Date Calculation Formula
Range("G1") = "Project End Date"
Range("G2:G" & lngLastRow).FormulaR1C1 = "=EDATE(RC6,120)"
Range("A1:G1").EntireColumn.AutoFit
   
'Add Conditional Formatting For Project End Dates
Range("G2:G" & lngLastRow).Select

With Selection

   .FormatConditions.Add _
      Type:=xlExpression, _
      Formula1:="=$G2<TODAY()"
   .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
   .FormatConditions(1).Interior.Color = 255
   .FormatConditions(1).StopIfTrue = False
   
End With

'Activate Cell A1, Freeze Cells, Add Autofilter
Range("A1").Select

With ActiveWindow

   .ScrollRow = 1
   .ScrollColumn = 1
   .SplitColumn = 0
   .SplitRow = 1
   .FreezePanes = True
   
End With

Range("A1").AutoFilter

End Sub

 

[richphi37]

Thank you so much,
That is awesome!!
I want you to know that you are my favorite person right now.
Hero of the BATTLE!!!