Format sheet based on column contents

  • Thread starter Thread starter C
  • Start date Start date
C

C

I would like to format my spreadsheet using VBA to make it more presentable.
My spreadsheet is set up as follows:

Col A Col B Col C Col D
Yes 1 Number Text
No 0 Number Text
No 5 Number Text

This report is generated by another application. It may contain 10 rows and
columns or it may contain 1000 rows and columns.

I need to format the data based on Cols A and B as follows:

If A = Yes--Format Column C:D Bold and Shade Gray.
If B = 1--Do nothing
If B = 2--Indent D 3 spaces
If B = 3--Indent D 6 spaces
If B = 0--Indent D 15 spaces

Thanks in advance for your help.
 
Give this macro a try...

Sub ChangeFormatting()
Dim X As Long, LastRow As Long
Const StartRow As Long = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
With Range("C" & StartRow & ":D" & LastRow)
.Font.Bold = False
.Interior.ColorIndex = xlColorIndexNone
End With
For X = StartRow To LastRow
If Cells(X, "A").Value = "Yes" Then
With Range("C" & X & ":D" & X)
.Font.Bold = True
.Interior.ColorIndex = 15
End With
Else
Select Case Cells(X, "B").Value
Case 0: Cells(X, "D").IndentLevel = 15
Case 1: Cells(X, "D").IndentLevel = 0
Case 2: Cells(X, "D").IndentLevel = 3
Case 3: Cells(X, "D").IndentLevel = 6
End Select
End If
Next
Application.ScreenUpdating = True
End Sub

I note your example shows a value of 5 which was not covered in your
requested indent levels and, so, it is not covered in my code above.
However, if you need to handle additional values for Column B, you can just
extend the Select Case block to account for them.
 
Back
Top