none
EXCEL Macro Help RRS feed

  • Question

  • Good Afternoon Everyone,

    I am trying to create a macro that will allow me to search column E in my excel spreadsheet for a list of 12 characters strings, then change the font color and fill background of these cells. 

    I have tried some Macro writing, it's my first day and I'm ready to throw the computer out of the window!

    Sub PushBackFormatting()
    'conditionally formatting the Values
        
      Columns(5).Select
       If Range("E5").End(xlDown).Select and Range.Value="09X260", Or"09X241", Or"09X297", Or"12X267", Or "07X223",Or,"09X327" Or,"12X242",Or"10X434",Or"10X439",Or"10X438",Or"10X437",Or"10X374",Or"10x243" Then
         With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        With Selection.Font
            .Color = -16711681
            .TintAndShade = 0
        End With
        End If
    End Sub

    Tuesday, April 3, 2018 7:28 PM

All replies

  • DRO,
    re:  "Its my first day"

    Actually there are 13 sets of text.
    '---
    Sub PushBackFormattingR1()
     Dim arrSize As Variant
     Dim rngCol As Excel.Range
     Dim rngLast As Excel.Range
     Dim rCell As Excel.Range
     Dim N As Long
     
    arrSize = Array("09X260", "09X241", "09X297", "12X267", "07X223", "09X327", _
             "12X242", "10X434", "10X439", "10X438", "10X437", "10X374", "10x243")
    Set rngLast = ActiveSheet.Cells(ActiveSheet.Rows.Count, 5).End(xlUp)
    Set rngCol = ActiveSheet.Range(ActiveSheet.Cells(1, 5), rngLast).Cells
    For Each rCell In rngCol
      For N = LBound(arrSize) To UBound(arrSize)
         If rCell.Value = arrSize(N) Then
            rCell.Interior.Color = vbRed
            rCell.Font.Color = vbBlue
            Exit For
         End If
      Next
    Next
    End Sub
    '---
    In my opinion, it is much more difficult to learn VBA from the internet vs. buying a book.
    Any John Walkenbach "Power Programming" book is good; version doesn't really matter when starting out.
    Used ones are just a few dollars or are sold for 1 cent + an inflated cost of shipping.

    '---
    Jim Cone
    https://goo.gl/IUQUN2

    Tuesday, April 3, 2018 8:04 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, April 4, 2018 1:52 AM
  • Hello DROSEATWORK,

    What's the state of the thread? Does James's code work for you?

    Besides looping through arrSize one by one, you could also filter the column using the array and then color the visible cells.

    Sub PushBackFormattingR2()
     Dim arrSize As Variant
     Dim rngCol As Excel.Range
     Dim rngLast As Excel.Range
     Dim rCell As Excel.Range
     Dim N As Long
     
    arrSize = Array("09X260", "10X438", "09X241", "09X297", "12X267", "07X223", "09X327", _
             "12X242", "10X434", "10X439", "10X437", "10X374", "10x243")
    Set rngLast = ActiveSheet.Cells(ActiveSheet.Rows.Count, 5).End(xlUp)
    Set rngCol = ActiveSheet.Range(ActiveSheet.Cells(1, 5), rngLast).Cells
    
    rngCol.AutoFilter 1, arrSize, xlFilterValues
    With rngCol.SpecialCells(xlCellTypeVisible)
    .Interior.Color = vbRed
    .Font.Color = vbBlue
    End With
    rngCol.AutoFilter
    End Sub

    Please feel free to follow up the thread to let us know if your issue is resolved.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 6, 2018 8:10 AM