Results 1 to 4 of 4

Thread: Correct syntax for row colour
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    984
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Correct syntax for row colour

    I have some code that copies data to other sheets depending on various options that are selected. This is part of my code that is working:

    Code:
                    Select Case tblrow.Range.Cells(1, 6).Value
                        Case "Yir"
                            DocYearName = tblrow.Range.Cells(1, 37).Value
                            'tblrow.RowColor = -65383
                        Case "Ang Wes", "Ang Riv"
                            DocYearName = tblrow.Range.Cells(1, 37).Value
                            'tblrow.RowColor = 0
                        Case Else
                            DocYearName = tblrow.Range.Cells(1, 36).Value
                            'tblrow.RowColor = 0
                    End Select
    If I remove the comments in the tblrow.rowcolor lines, I get the error of object doesn't support this property or method. What is the correct syntax to set the row colour?

  2. #2
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    984
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Correct syntax for row colour

    I forgot to mention the start of the procedure has a few bits you need to know.

    Code:
    Sub cmdCopy()
            Dim wsDst As Worksheet, wsSrc As Worksheet, tblrow As ListRow
            Dim Combo As String, sht As Worksheet, tbl As ListObject
            Dim LastRow As Long, DocYearName As String, lr As Long
            Dim RowColor As Long, w As Window
                Application.ScreenUpdating = False
            'assign values to variables
            Set tbl = ThisWorkbook.Worksheets("Costing_tool").ListObjects("tblCosting")
            Set sht = ThisWorkbook.Worksheets("Costing_tool")
            For Each tblrow In tbl.ListRows
                If tblrow.Range.Cells(1, 1).Value = "" Or tblrow.Range.Cells(1, 5).Value = "" Or tblrow.Range.Cells(1, 6).Value = "" Then
                    MsgBox "The Date, Service or Requesting Organisation has not been entered for every record in the table"
                    Exit Sub
                End If
            Next tblrow
            For Each tblrow In tbl.ListRows
                Combo = tblrow.Range.Cells(1, 26).Value
                
                    Select Case tblrow.Range.Cells(1, 6).Value
                        Case "Yir"
                            DocYearName = tblrow.Range.Cells(1, 37).Value
                            tblrow.RowColor = -65383
                        Case "Ang Wes", "Ang Riv"
                            DocYearName = tblrow.Range.Cells(1, 37).Value
                            'tblrow.RowColor = 0
                        Case Else
                            DocYearName = tblrow.Range.Cells(1, 36).Value
                            'tblrow.RowColor = 0
                    End Select

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,312
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Correct syntax for row colour

    Try it like this...

    tblrow.Range.Interior.Color = -65383
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    984
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Correct syntax for row colour

    Here is the entire procedure as I think I have it in the wrong place

    Code:
    Sub cmdCopy()
            Dim wsDst As Worksheet, wsSrc As Worksheet, tblrow As ListRow
            Dim Combo As String, sht As Worksheet, tbl As ListObject
            Dim LastRow As Long, DocYearName As String, lr As Long
            Dim RowColor As Long, w As Window
                Application.ScreenUpdating = False
            'assign values to variables
            Set tbl = ThisWorkbook.Worksheets("Costing_tool").ListObjects("tblCosting")
            Set sht = ThisWorkbook.Worksheets("Costing_tool")
            For Each tblrow In tbl.ListRows
                If tblrow.Range.Cells(1, 1).Value = "" Or tblrow.Range.Cells(1, 5).Value = "" Or tblrow.Range.Cells(1, 6).Value = "" Then
                    MsgBox "The Date, Service or Requesting Organisation has not been entered for every record in the table"
                    Exit Sub
                End If
            Next tblrow
            For Each tblrow In tbl.ListRows
                Combo = tblrow.Range.Cells(1, 26).Value
                
                    Select Case tblrow.Range.Cells(1, 6).Value
                        Case "Yir"
                            DocYearName = tblrow.Range.Cells(1, 37).Value
                            'tblrow.RowColor = -65383
                        Case "Ang Wes", "Ang Riv"
                            DocYearName = tblrow.Range.Cells(1, 37).Value
                            'tblrow.RowColor = 0
                        Case Else
                            DocYearName = tblrow.Range.Cells(1, 36).Value
                            'tblrow.RowColor = 0
                    End Select
                If Not isFileOpen(DocYearName & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & DocYearName & ".xlsm"
    
                Set wsDst = Workbooks(DocYearName).Worksheets(Combo)
                 lr = wsDst.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
                 With wsDst
                        'This copies the first 16 columns, i.e. A:J, of the current row of the table to column A in the destination sheet.
                        tblrow.Range.Resize(, 16).Copy
                        'This pastes in the figures in the first 10 columns starting in column A
                        .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
                        'Overwrites the numbers pasted to column I with a formula
                        .Range("I" & .Range("I" & .Rows.Count).End(xlUp).Row).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                        'Overwrites the numbers pasted to column J with a formula
                        .Range("J" & .Range("J" & .Rows.Count).End(xlUp).Row).Formula = "=RC[-1]+RC[-2]"
                        'sort procedure copied from vba
                        If tblrow.Range(, 6) = "Yir" Then
                            .Cells.Font.RowColor = -65383
                        End If
                        wsDst.Sort.SortFields.Clear
                        wsDst.Sort.SortFields.Add Key:=Range("A4:A" & lr), _
                            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                                With Workbooks(DocYearName).Worksheets(Combo).Sort
                                    .SetRange Range("A3:AK" & lr)
                                    .header = xlYes
                                    .MatchCase = False
                                    .Orientation = xlTopToBottom
                                    .SortMethod = xlPinYin
                                    .Apply
                                End With
                End With
            Next tblrow
            Application.CutCopyMode = False
            Application.ScreenUpdating = True
    End Sub

    • For every row in tblCosting, Column 26 contains the name of the sheet that the row will be copied to.
    • The file names of the two different types of files are stored in cells 36 and 37 for each row.


    Setting the row colour of the destination workbook, if column F has the string "Yir" needs to be further down, but I don't know where and I am not sure of the syntax.
    Could you help me please Rick?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •