Conditional Formatting not being copied when new row is added to a table

[Robert_Conklin]

I have some code that creates a new line in 3 different tables on three different worksheets in a workbook when data submitted through a user-form. All of the formulae are copied from the line above and all of the data from the user-form writes to the correct locations, but I have a total of 5 columns that contain condition number formatting that will not copy down to the new row in the tables.

I have tried changing the number format to something different, saving/closing the workbook. Then re-opening the workbook and re-applying the conditional number formatting to the entire column data range at the same time as suggested in the link below:

https://www.excelcampus.com/tables/t...er-formatting/

That did not fix my problem. Below is the code I am using:

Option ExplicitDim iRow As Long, i As Long, j As Long
Dim ctrl As control
Dim collist As Collection
Dim tbx As OLEObject
Dim Row As ListRow
Private Sub cb01_Click()
Dim oNewRow As ListRow


With Sheets("PGS Score Card").ListObjects("PGSSC_tbl")
     Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
         With oNewRow.Range
            .Cells(1, 2) = Me.tbx01
            .Cells(1, 4) = Me.tbx21
            .Cells(1, 5) = Me.tbx02
            .Cells(1, 6) = Me.tbx18
End With


With Sheets("PGSSavingsTimeline(Projections)").ListObjects("PGSSTP_tbl")
     Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
         With oNewRow.Range
            .Cells(1, 1) = Me.cbx13
            .Cells(1, 2) = Me.tbx01
            .Cells(1, 3) = Me.cbx02
            .Cells(1, 4) = Me.tbx21
            .Cells(1, 5) = Me.tbx22
            .Cells(1, 6) = Me.tbx03
            .Cells(1, 7) = Me.cbx04
            .Cells(1, 8) = Me.cbx05
            .Cells(1, 9) = Me.cbx06
            .Cells(1, 10) = Me.cbx07
            .Cells(1, 11) = Me.tbx04
            .Cells(1, 12) = Me.cbx08
            .Cells(1, 13) = Me.tbx26
            .Cells(1, 14) = Me.tbx05
            .Cells(1, 15) = Me.tbx06
            .Cells(1, 16) = Me.tbx07
            .Cells(1, 17) = Me.cbx09
            .Cells(1, 18) = Me.tbx09
            .Cells(1, 19) = Me.tbx08
            .Cells(1, 20) = Me.tbx27
            .Cells(1, 21) = Me.tbx23
            .Cells(1, 22) = Me.tbx24
End With


With Sheets("PG&S Savings Timeline (Roll-up)").ListObjects("PGSSTRu_tbl")
     Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
         With oNewRow.Range
            .Cells(1, 2) = Me.tbx01
            .Cells(1, 8) = Me.cbx10
            .Cells(1, 9) = Me.cbx12
            .Cells(1, 10) = Me.tbx10
            .Cells(1, 11) = Me.cbx14
            .Cells(1, 12) = Me.tbx11
            .Cells(1, 13) = Me.cbx11
            .Cells(1, 14) = Me.tbx12
            .Cells(1, 26) = Me.tbx25
            .Cells(1, 27) = Me.tbx19
            .Cells(1, 29) = Me.tbx15
            .Cells(1, 33) = Me.tbx20
            .Cells(1, 34) = Me.tbx17
            .Cells(1, 35) = Me.tbx14
End With


  For Each ctrl In Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then ctrl.Value = ""
Next ctrl
    LB_01.ListIndex = -1
    Call UserForm_Initialize
       End With
    End With
End With
End Sub

Any suggestions would be GREATLY appreciated!

 

[Robert_Conklin]

After learning that Conditional Formatting was not the same as Custom Number Formatting were not the same thing, I must add, that the conditional formatting is not the problem, it is the Custom Number Formatting that is not copying down from the line above. I do however, have condition formatting rules on each of the tables.

 

[Robert_Conklin]

The solution to this problem was the .Value was missing behind my field names. Below is the corrected code.

Private Sub cb01_Click()Dim oNewRow As ListRow


With Sheets("PGS Score Card").ListObjects("PGSSC_tbl")
     Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
         
     With oNewRow.Range
          .Cells(1, 2) = Me.tbx01.Value
          .Cells(1, 4) = Me.tbx21.Value
          .Cells(1, 5) = Me.tbx02.Value
          .Cells(1, 6) = Me.tbx18.Value
 
End With


With Sheets("PGSSavingsTimeline(Projections)").ListObjects("PGSSTP_tbl")
     Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
         
         With oNewRow.Range
            .Cells(1, 1) = Me.cbx13.Value
            .Cells(1, 2) = Me.tbx01.Value
            .Cells(1, 3) = Me.cbx02.Value
            .Cells(1, 4) = Me.tbx21.Value
            .Cells(1, 5) = Me.tbx22.Value
            .Cells(1, 6) = Me.tbx03.Value
            .Cells(1, 7) = Me.cbx04.Value
            .Cells(1, 8) = Me.cbx05.Value
            .Cells(1, 9) = Me.cbx06.Value
            .Cells(1, 10) = Me.cbx07.Value
            .Cells(1, 11) = Me.tbx04.Value
            .Cells(1, 12) = Me.cbx08.Value
            .Cells(1, 13) = Me.tbx26.Value
            .Cells(1, 14) = Me.tbx05.Value
            .Cells(1, 15) = Me.tbx06.Value
            .Cells(1, 16) = Me.tbx07.Value
            .Cells(1, 17) = Me.cbx09.Value
            .Cells(1, 18) = Me.tbx09.Value
            .Cells(1, 19) = Me.tbx08.Value
            .Cells(1, 20) = Me.tbx27.Value
            .Cells(1, 21) = Me.tbx23.Value
            .Cells(1, 22) = Me.tbx24.Value
End With


With Sheets("PG&S Savings Timeline (Roll-up)").ListObjects("PGSSTRu_tbl")
     Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
         With oNewRow.Range
            .Cells(1, 2) = Me.tbx01.Value
            .Cells(1, 8) = Me.cbx10.Value
            .Cells(1, 9) = Me.cbx12.Value
            .Cells(1, 10) = Me.tbx10.Value
            .Cells(1, 11) = Me.cbx14.Value
            .Cells(1, 12) = Me.tbx11.Value
            .Cells(1, 13) = Me.cbx11.Value
            .Cells(1, 14) = Me.tbx12.Value
            .Cells(1, 26) = Me.tbx25.Value
            .Cells(1, 27) = Me.tbx19.Value
            .Cells(1, 29) = Me.tbx15.Value
            .Cells(1, 33) = Me.tbx20.Value
            .Cells(1, 34) = Me.tbx17.Value
            .Cells(1, 35) = Me.tbx14.Value
End With


  For Each ctrl In Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then ctrl.Value = ""
Next ctrl
    LB_01.ListIndex = -1
    Call UserForm_Initialize
       End With
    End With
End With
End Sub