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.


  • 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


    Added to the Workbook_SheetSelectionChange event and it appears to working.

    Thank you for the quick response.

Participate now!

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