Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 33

Thread: VBA to see if a column contains a certain string
Thanks Thanks: 0 Likes Likes: 0

  1. #21
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,805
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA to see if a column contains a certain string

    thinking !!!

    OK, replace the code that is giving us problems with the original code I posted
    Put it in the same location

    Code:
    Sub MM1()
    Dim lr As Long, r As Long
    lr = Cells(Rows.Count, "F").End(xlUp).Row
    For r = 1 To lr
        If Range("F" & r).Value = "Yir" Then
           Range("F" & r).EntireRow.font.ColorIndex = 3
        End If
    Next r
    End Sub
    Last edited by Michael M; Aug 20th, 2019 at 09:06 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

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

    Default Re: VBA to see if a column contains a certain string

    Thanks for all this help Michael,

    I put that code in and no colours got changed. I ran it a second time and got the error subscript out of range with this line highlighted

    Code:
    Range("F" & r).EntireRow.Font.ColorIndex = -65383
    This is my total procedure:

    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, r As Long
                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
                        Case "Ang Wes", "Ang Riv"
                            DocYearName = tblrow.Range.Cells(1, 37).Value
                        Case Else
                            DocYearName = tblrow.Range.Cells(1, 36).Value
                    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]"
    
                            lr = Cells(Rows.Count, "F").End(xlUp).Row
                            For r = 1 To lr
                                If Range("F" & r).Value = "Yir" Then
                                   Range("F" & r).EntireRow.Font.ColorIndex = -65383
                                End If
                            Next r
                        'sort procedure copied from vba
                        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
    Last edited by dpaton05; Aug 20th, 2019 at 09:31 PM.

  3. #23
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,805
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA to see if a column contains a certain string

    Why did you modify this line ?
    Code:
    Range("F" & r).EntireRow.Font.ColorIndex = -65383
    colorindex needs to be a number between 1 - 56, although it is probably a greater number on later versions !!
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #24
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,805
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA to see if a column contains a certain string

    try this slight mod

    Code:
    lr = .Cells(Rows.Count, "F").End(xlUp).Row
           For r = 1 To lr
               If .Range("F" & r).Value = "Yir" Then .Range("F" & r).EntireRow.Font.ColorIndex = 3
           Next r
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

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

    Default Re: VBA to see if a column contains a certain string

    That seems to have worked, thank you so much Michael

  6. #26
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,805
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA to see if a column contains a certain string

    OK....glad it worked...
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

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

    Default Re: VBA to see if a column contains a certain string

    Just noticed that is has appeared to have changed the colour of every cell in column F of the source workbook to be blue, where the rows around it are alternate colours. How can I make it the same as the rows around it?

  8. #28
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,805
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA to see if a column contains a certain string

    what colour are the rows around it ??
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

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

    Default Re: VBA to see if a column contains a certain string

    White and light blue that appears to be the standard colour of a table.

  10. #30
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,805
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA to see if a column contains a certain string

    The code I provided should only affect the Dest worksheet AND only the font, not the cell colour ??
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

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
  •