Prev: RegExp not matching what I want
Next: Extracting date file modified from a text file.
From: C on 19 Apr 2010 11:09
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.
From: Rick Rothstein on 19 Apr 2010 12:51
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.

--
Rick (MVP - Excel)



"C" <C(a)discussions.microsoft.com> wrote in message
news:7D8FC34E-6AE9-45FF-80EF-6B5F840C5802(a)microsoft.com...
> 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.

 | 
Pages: 1
Prev: RegExp not matching what I want
Next: Extracting date file modified from a text file.