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

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
120
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:

Code:
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

Board Regular
Joined
Jun 19, 2017
Messages
120
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

Board Regular
Joined
Jun 19, 2017
Messages
120
The solution to this problem was the .Value was missing behind my field names. Below is the corrected code.

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
 

Forum statistics

Threads
1,082,257
Messages
5,364,074
Members
400,778
Latest member
Canadian Sal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top