Format sheet based on column contents



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.

Rick Rothstein

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
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
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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question