VBA code to apply conditional formatting to last row with data in a table

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
871
Office Version
  1. 365
Platform
  1. Windows
I have conditional formatting in a table. The table is called NCRLog.
What would be the VBA code to find the last cell in Column A with data and apply the conditional formatting for only that row (from column A to Column J)?

I think it would start something like: Range("NCRLog").Cells(1, 1).End(xlDown)

But I really don't know what to do after.

Thank you

Carla
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try this macro:
Code:
Public Sub Change_CF_To_Last_Data_Row_in_Table()

    Dim table As ListObject
    Dim lastDataRow As Long
    Dim cf As FormatCondition
    
    Set table = ActiveSheet.ListObjects("NCRLog")
    
    With table.DataBodyRange
        
        'Get existing conditional formatting rule in table
        Set cf = .FormatConditions(1)
        
        'Find last row with data in column 1 of table
        lastDataRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        MsgBox "Cells in last row with data in NCRlog table = " & .Cells(lastDataRow - .Row + 1, 1).Resize(1, .Columns.Count).Address
        
        'Change conditional formatting to last data row
        cf.ModifyAppliesToRange .Cells(lastDataRow - .Row + 1, 1).Resize(1, .Columns.Count)
        
    End With
    
End Sub
 
Upvote 0
Bug fix

My macro doesn't apply the conditional formatting to the correct row if the last row with data is the last row in the table or if the first column in the table is empty. Add this code after the lastDataRow = line to fix this bug:

Code:
        If lastDataRow = .Row - 1 Then
            If IsEmpty(.Cells(lastDataRow + 1, 1).Value) Then
                lastDataRow = .Row 'First column empty, so use first row
            Else
                lastDataRow = .Row + .Rows.Count - 1 'Last row
            End If
        End If
 
Upvote 0
This did not format the last row with data. It also displayed a prompt of what the last row of data was which will just confuse users.

See below my code so far. What I want to do is when I paste the information into the NCRLog aka:

Range("NCRLog").Cells(1, 1).End(xlDown).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

I want to pull down the conditional formatting from the row above it without any additional prompts.

Hope this helps explain what I am looking for a bit better. Full Code is below.

Thank you

Carla

Sub NCR()
'
' NCR Macro
'
Dim Msg As String, Ans As Variant


Msg = "Would you like to update the NCR Log with this Data?"


Ans = MsgBox(Msg, vbYesNo)


Select Case Ans


Case vbYes
Sheets("New NCR").Select
Range("A2:J2").Select
Selection.Copy
Sheets("NCR Log").Select
Range("NCRLog").Cells(1, 1).End(xlDown).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A1").Select
Sheets("New NCR").Select
Range("B2:J2").Select
Selection.ClearContents
Range("B2").Select
Sheets("Graphs").Select
ActiveSheet.PivotTables("NCRTable").PivotCache.Refresh
ActiveSheet.ChartObjects("NCRChart").Activate
ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh
Sheets("New NCR").Select
Range("B2").Select


MsgBox "Update Complete"


Case vbNo
GoTo Quit:
End Select


Quit:


'
End Sub
 
Upvote 0
I want to pull down the conditional formatting from the row above it without any additional prompts.
I included the MsgBox prompt to allow you to confirm that the code was correctly finding the last row with data. Just comment out that line or delete it when you don't need the confirmation.

Does the code find the last row with data or not? If not then that could explain why it didn't change the current conditional formatting to that row.

Add this line after the Set cf line to display the cell addresses of the current conditional formatting rule:

Code:
        MsgBox "Current conditional formatting rule applies to " & cf.AppliesTo.Address
Note - the code assumes there is only one conditional formatting rule in the table - the .FormatConditions(1). Is that assumption correct?

By 'pull down', do you mean fill down, i.e. copy the CF from the row above, so that the CF applies to 2 or more rows? From your OP I thought you wanted to move the CF so that it applies only to the last row.
 
Upvote 0
There are multiple conditional formats, sorry. However the table seems to be doing it itself now, not sure why but it is working. I will keep your code in mind if it stops.

Thank you very much!
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top