none
Colour rows based on different text criteria; adapt to new iterations of workbook RRS feed

  • Question

  • Hi, I need help developing a macro that can do a couple different things.

    Situation: Someone else maintains a project spreadsheet in which projects (rows) are frequently added or removed; projects are tracked by number in column A. I download this spreadsheet regularly to work from the newest iteration and track work on the projects. I want to highlight rows for which projects that have been started (let's say in green) and projects that have been finished (lets say dark blue).

    Macro: The only way to consistently track the projects is by project number, as line numbers change with iterations. So I was thinking of using a macro where I could set conditional formatting based on whether the line includes a specified project number. I could update this macro by adding new numbers in the conditions when needed, and run the macro in the new iteration of the workbook.

    For example, if the cell in column A = {"1","3",1094"} then the row will be green, OR if the cell in column A = {"2","10","82"} then the row will be dark blue. If I start project 4, then I will edit the macro and add "4" to the "green condition" or if I complete project 1094, I will move "1094" to the "blue" condition; then save the macro.

    I tried making one myself, but could only hide (not colour) rows based on one (not multiple) values. Can anyone help me develop a macro for this?

    Thank you.

    Friday, May 29, 2015 2:00 PM

Answers

  • Hi Cviita,

    Based on my understanding, you want to add condition format via the cell value in column A.

    A recommend way is that there should be a status column for project and we can add condition format via the status column. And here is an sample that add condition format to the range("A2:F9"), it would add an background color the entire row for the cell in column D is equal "end":

    Sub addConditionFormat()
     With Range("A2:F9").FormatConditions.add(Type:=xlExpression, Formula1:="=$D2=""end""")
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(100, 100, 100)
            .TintAndShade = 0
         End With
       
     End With
    End Sub

    You can get more detail about contition format from link below:
    FormatConditions.Add Method

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 2, 2015 6:50 AM
    Moderator

All replies

  • Hi,

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

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    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.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Monday, June 1, 2015 5:57 AM
  • Hi Cviita,

    Based on my understanding, you want to add condition format via the cell value in column A.

    A recommend way is that there should be a status column for project and we can add condition format via the status column. And here is an sample that add condition format to the range("A2:F9"), it would add an background color the entire row for the cell in column D is equal "end":

    Sub addConditionFormat()
     With Range("A2:F9").FormatConditions.add(Type:=xlExpression, Formula1:="=$D2=""end""")
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(100, 100, 100)
            .TintAndShade = 0
         End With
       
     End With
    End Sub

    You can get more detail about contition format from link below:
    FormatConditions.Add Method

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 2, 2015 6:50 AM
    Moderator