Applying intuitive formatting to a spreadsheet of client data

Legion_1984

New Member
Joined
Aug 9, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Web
I have a spreadsheet with 14,500 lines of data, I was able to apply VBA codes to remove blank rows and columns to get it down to 8,000 but I need to apply formatting to the rows (column A if it contains an account # to shade fill the whole row in the selection) and applying a top and thick bottom border to any row with "Account Total" in Column C

Capture of a sample of the data attached - this is how I would like to carry the formatting through the entire selection area - any help / guidance would be a huge help! Thanks in advance.
 

Attachments

  • Capture.JPG
    Capture.JPG
    101.6 KB · Views: 2

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,246
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Typically you would use Excel's built-in Conditional Formatting for that sort of job since it automatically adjusts if the data changes. However, CF does not cater for thick borders.

In any case you may have been specifically looking for more vba to add to your existing code. You could give this a try with a copy of your workbook

VBA Code:
Sub ApplyFormatting()
  Application.ScreenUpdating = False
  With Intersect(ActiveSheet.UsedRange, Rows("4:" & Range("C" & Rows.Count).End(xlUp).Row))
    .AutoFilter Field:=1, Criteria1:="<>"
    .Offset(1).Resize(.Rows.Count - 1).Interior.Color = 14277081
    .AutoFilter Field:=1
    .AutoFilter Field:=3, Criteria1:="Account Total"
    With .Offset(1).Resize(.Rows.Count - 1)
      .Borders(xlEdgeTop).LineStyle = xlContinuous
      .Borders(xlEdgeBottom).LineStyle = xlContinuous
      .Borders(xlEdgeBottom).Weight = xlThick
    End With
    .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub
 
Solution

Legion_1984

New Member
Joined
Aug 9, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Sub ApplyFormatting() Application.ScreenUpdating = False With Intersect(ActiveSheet.UsedRange, Rows("4:" & Range("C" & Rows.Count).End(xlUp).Row)) .AutoFilter Field:=1, Criteria1:="<>" .Offset(1).Resize(.Rows.Count - 1).Interior.Color = 14277081 .AutoFilter Field:=1 .AutoFilter Field:=3, Criteria1:="Account Total" With .Offset(1).Resize(.Rows.Count - 1) .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeBottom).Weight = xlThick End With .Parent.AutoFilterMode = False End With Application.ScreenUpdating = True End Sub
THANK YOU! I tried the conditional formatting for the shading but I think my formula logic was off - it would apply to the line above or below the account # fields holding data, then I considered that combining them in VBA might simplify the editing.
Seriously thank you - this has saved hours!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,246
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Forum statistics

Threads
1,144,670
Messages
5,725,677
Members
422,635
Latest member
crisis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top