Hi All,
I have a worksheet divided into 4 quadrants. In each quadrant for a selected range, I have to perform the below activities.
In Q1 (A4:B23): All the empty cells should go at the bottom. i.e, if A9:B9 is empty, then the rows below should move up pushing all the empty cells to the bottom within the Q1.
In Q2 (D4: AE12): All cells except the last column should not have any background color and if any cell is empty in row D8:AE8, the entire column should be deleted.
In Q3 (A25:B75): All cells should not have any background color and if any cell is empty in range A25:A75, the entire row should be deleted.
In Q4 (D25:AE75): The cell with any alphabet should have the color which is given in Q1 for that alphabet. Eg: "A" in Q1 is pink so all cells which have "A" in Q4 should have background color as pink.
Note: The ranges mentioned above varies from document to document.
I have written a code for Q2,Q3 as below but I am getting a run time error.
Sub CleanRoadMap()
' To clear cell color
Dim C As Range, ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Roadmap" Then
Call ClearCell(ws, C)
End If
Next ws
End Sub
Sub ClearCell(ByVal ws As Worksheet, ByVal C As Range)
N = ws.Cells.Find("SUBID", , , , xlByRows, xlPrevious).Row
LR = Cells(Rows.Count, "A").End(xlUp).Row
M = ws.Cells.Find("None", , , , xlByColumns, xlPrevious).Column
S = ws.Cells.Find("SCRN", , , , xlByRows, xlPrevious).Row
For Each C In ws.Range(Cells(N, 1), Cells(LR, 1))
C.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
C.Interior.ColorIndex = x1None
Next C
For Each C In ws.Range(Cells(N, 2), Cells(LR, 2))
C.Interior.ColorIndex = x1None
Next C
For Each C In ws.Range(Cells(4, 4), Cells(S - 1, M - 1))
C.Interior.ColorIndex = x1None
Next C
For Each C In ws.Range(Cells(S, 4), Cells(S, M - 1))
C.SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
C.Interior.ColorIndex = x1None
Next C
For Each C In ws.Range(Cells(S + 1, 4), Cells(S + 1, M - 1))
C.Interior.ColorIndex = x1None
Next C
For Each C In ws.Range(Cells(4, 2), Cells(N - 2, 2))
C.Interior.ColorIndex = x1None
Next C
End Sub
Please help!!!
Bookmarks