[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Excel Macro - Need to fill only those columns/rows that exist in my Table

I am using the following code to format rows in a pivot table based on the contents of two different columns. I would like this formatting to only take place on the rows/columns that exist within the table itself but am not quite sure how to modify this to make this happen.

The formatting currently works properly for the rows, but all columns that exist in the spreadsheet get formatted with the designated bg color as well. I'd like to know how I can prevent those columns that are not a part of my table from getting formatted. I know that this code is selecting the entire sheet which is likely the problem, can anyone advise how to modify this to apply to the table only?

Would really appreciate some feedback/guidance
Sub FormatIssuesList()
    FormatByType
    FormatByStatus
End Sub

Sub FormatByType()

    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    
    

    With Sheets("Escalations - Data")
    'With Table("Table1")

        'Select the sheet so we can change the window view
        .Select

        'Go to normal view for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, again for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row


        'Loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'Check the values in the J column in this example
            '[Note: alternatively, if whole row is named,
            'could use that name if it makes things clearer:
            'e.g. With .Cells(Lrow, "First_Name")
            'Also With .Cells(Lrow, 10) works, too, if row/column math desired.
            With .Cells(Lrow, "D")
            
                'Safety first
                If Not IsError(.Value) Then
                    
                    'Do tests and set BG colors appropriately
                    Select Case .Value
                            Case "Assistance"
                                Rows(Lrow).Interior.Color = RGB(148, 138, 84)
                            Case "Enhancement Request"
                                Rows(Lrow).Interior.Color = RGB(51, 153, 102)
                    End Select

              End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

Sub FormatByStatus()

    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    With Sheets("Escalations - Data")

        'Select the sheet so we can change the window view
        .Select

        'Go to normal view for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, again for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'Loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'Check the values in the J column in this example
            '[Note: alternatively, if whole row is named,
            'could use that name if it makes things clearer:
            'e.g. With .Cells(Lrow, "First_Name")
            'Also With .Cells(Lrow, 10) works, too, if row/column math desired.
            With .Cells(Lrow, "E")
            
                'Safety first
                If Not IsError(.Value) Then
                    
                    'Do tests and set BG colors appropriately
                    Select Case .Value
                            Case "Closed"
                                Rows(Lrow).Interior.Color = RGB(150, 150, 150)
                            Case "PCI"
                                Rows(Lrow).Interior.Color = RGB(216, 228, 188)
                            Case "Closed Pending"
                                Rows(Lrow).Interior.Color = RGB(255, 255, 153)
                            Case "Rejected"
                                Rows(Lrow).Interior.Color = RGB(150, 150, 150)
                    End Select

              End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

Open in new window

0
bereal53
Asked:
bereal53
  • 2
1 Solution
 
IndiaRahulCommented:
Hi bereal53,
if possible can you please attach the file with dummy numbers to replicate the question..
0
 
bereal53Author Commented:
Sorry for the delayed response. I ended up developing a workaround. I don't work with Excel alot and had forgotten that I could record a macro and just steal the code from that :). Now, once the formatting is applied (as shown in the first post) I just call a second routine to remove the additional formatting I don't want. I'm sure that there is a better way to do this, but unfortunately I didn't have time to dig any further.

This sub removes the fill from all columns to the right of the range shown in the first line, and then selects the cell that is designated in the last line.

Sub NoFill()

    Columns("AF:AJ").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
   
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
   
Range("A10").Select
   
End Sub
0
 
bereal53Author Commented:
This is a workaround not a true solution to the original question.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now