Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format sheet based on column contents
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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I sort contents of one column based on the contents ofanother column? | Excel Programming | |||
Fill a column with the contents of another column based on a choic | Excel Discussion (Misc queries) | |||
Can I format a row based on the contents of one cell? | Excel Discussion (Misc queries) | |||
move contents of column C based on criteria related to column A | Excel Discussion (Misc queries) | |||
Lookup cell contents in on sheet based on a formula in second sheet | Excel Worksheet Functions |