Dynamic formatting specific cells in VBA

Question

I am new to VBA and cannot figure out how to dynamically format the total sum and fee cells of a macro I recorded. I need to add highlight, bold, change style to currency, change number format, and borders. The reports are not always the same so the formatting needs to be dynamic. The screenshot below shows what I like the final result to look like. Any help is much appreciated!

Below is what I currently have so far,

   Range("M1").Activate
   ActiveWindow.SmallScroll ToRight:=9
   Range("A:A,M:M,Q:T,V:V").Select
   Range("V1").Activate
   ActiveWindow.SmallScroll ToRight:=9
   Range("A:A,M:M,Q:T,V:V,X:X,AA:AB").Select
   Range("AA1").Activate
   Selection.Delete Shift:=xlToLeft
   Columns("V:BM").Select
   Selection.Delete Shift:=xlToLeft
   ActiveWindow.ScrollColumn = 1
   Range("A1").Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlToRight)).Select
   Selection.Columns.AutoFit
   ActiveWindow.ScrollColumn = 13
   ActiveWindow.ScrollColumn = 19
   ActiveWindow.ScrollColumn = 11
   ActiveWindow.ScrollColumn = 8
   ActiveWindow.SmallScroll ToRight:=3
   ActiveWindow.ScrollColumn = 8
   ActiveWindow.SmallScroll Down:=129
   Lastrow = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 10).End(xlUp).Row
   ThisWorkbook.Sheets("sheet1").Range("I" & Lastrow + 1) = "Total Sales"
   ThisWorkbook.Sheets("sheet1").Range("I" & Lastrow + 2) = "Total Fee"
   ThisWorkbook.Sheets("sheet1").Range("j" & Lastrow + 1) = WorksheetFunction.Sum(ThisWorkbook.Sheets("sheet1").Range("j2:j" & Lastrow))
   ThisWorkbook.Sheets("sheet1").Range("j" & Lastrow + 2) = WorksheetFunction.Sum(ThisWorkbook.Sheets("sheet1").Range("j2:j" & Lastrow)) * 0.01

End Sub

What do you mean by *"needs to be dynamic"*? If you need it to find the `Total Sales` cell and then format that cell and the 3 cells around the right/bottom, then you need to use the `Find()` method first to find that cell (there are tutorials for that). • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Jun 01 '22 at 09:03
Dynamic meaning the total cells will not always be in the same range. Some reports could 50 rows and others could be 60. Not sure if that clarifies your question. — jon, Jun 01 '22 at 09:55
Then proceed as I mentioned in the comment. Use the [Range.Find method](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find) to find the `Total Sales` cell and use `.Resize(2, 2)` to get that 4x4 block of cells outgoing from the `Total Sales` cell. Then you can format it as desired. Give it a try. — Pᴇʜ, Jun 01 '22 at 10:00

score 0 · Accepted Answer · answered Jun 01 '22 at 10:52

Arrange Data Dynamically

Sub ArrangeData()
   
   With ThisWorkbook.Worksheets("Sheet1")
       
       ' Delete columns.
       .Range("A:A,M:M,Q:T,V:V,X:X,AA:AB,AF:BW").Delete Shift:=xlToLeft
   
       ' Totals
       
       ' Calculate
       Dim lRow As Long: lRow = .Cells(.Rows.Count, "J").End(xlUp).Row
       Dim Total As Double: Total = Application.Sum(.Range("J2:J" & lRow))
       
       ' Reference the totals' range (4 cells).
       With .Range("I" & lRow + 1, "J" & lRow + 2)
           
           ' Write.
           .Cells(1).Value = "Total Sales"
           .Cells(2).Value = Total
           .Cells(3).Value = "Total Fee"
           .Cells(4).Value = Total * 0.01
       
           ' Format
           .Font.Bold = True
           .Interior.Color = vbYellow ' 65535
           .BorderAround xlContinuous
           .Borders(xlInsideVertical).LineStyle = xlContinuous
           .Borders(xlInsideHorizontal).LineStyle = xlContinuous
           .Columns(2).NumberFormat = "[$$-en-US]#,##0.00"
       
       End With
       
       ' Autofit columns.
       Dim lCol As Long: lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
       .Range("A1", .Cells(1, lCol)).EntireColumn.AutoFit
   
   End With
   
End Sub

Wow, That worked!! I spent a significant amount of time searching through forums, this is amazing. It's simple and easy to understand. Thank you so much!! — jon, Jun 01 '22 at 14:05

Dynamic formatting specific cells in VBA

1 Answers1

Arrange Data Dynamically