Change cell color if row contains color

  • Support,


    Want columns A and B to be highlighted the same color if E through V contains that color.
    Only colors that matter are yellow ColorIndex = 3 and red ColorIndex = 6.
    If the row contains both colors red will be displayed.
    If the row does not contain red or yellow then no change will be made to the A or B column.


    To better explain:
    In the attached spreadsheet A5 and B5 would be yellow because E5 is yellow.
    In the attached spreadsheet A14 and B14 would be red because I14 is red.


    Tried a few different things from the site here but was unable to find anything that I could get to work.
    Tried using CF but could not figure out how to match ColorIndex.


    Have no issue using CF if that is solution.


    There is a Workbook_SheetSelectionChange that captures the color change and counts the cells.
    Check Compliance button is redundant and will probably be removed.
    There are is a dynamic range aRange that contains E5-V32.


    If anything is unclear or there are any questions please let me know.


    Micah

  • Re: Change cell color if row contains color


    Hi Zappd,


    You can try this code.


    Be advised I have written almost no code for months so I'm very rusty, there's probably a better way to do this.


    It works off of the worksheet change event, you could put it in a regular module.


    It's lightly tested and there's no error handling.


    Good Luck :cheers:


  • Re: Change cell color if row contains color


    Bruce,


    Added to the Workbook_SheetSelectionChange event and it appears to working.


    Thank you for the quick response.
    Micah

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!