VBA to identify colored text in every 5 cells of a column

  • Hello,


    I have a file which starts data in Cell E8 and continues down and to the right in varying columns in rows.

    I created a macro to color certain data in the file yellow


    I am now writing a macro to look at cells E8-E12, and if the cells contain 3 yellow colored cells, all 5 cells turn red.

    This will continue through E13-E17 and also loop through all relevant columns that contain data.


    In short starting at E8 it should check every 5 cells in a row for 3 colored data points. If there are three colored data points all 5 cells turn red.


    I don't understand how to make my For statement jump down 5 columns for each loop. Could I have some advice?


    Edited once, last by KjBox ().

  • Try changing

    Code
    For i = 5 To lastcolumn

    to

    Code
    For i = 5 To lastcolumn Step 5

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Above is close to what I want but, the Step is through the columns. I would like the step to be through the rows.


    I know I need a similar function to step "down" but I'm not sure how to write it.

  • Hi,


    KjBox gave you the solution ....


    Below an example for a double loop :


    Hope this will help :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thank you, I'm not the best at VBA so I think I was having a hard time understanding it.

    I ended up making a double loop and used offset to accomplish the same thing. Although the Step command seem simpler.


  • Congratulations on reaching your own solution !!!


    We are, all of us, embarked on a continuous (and fun) Learning journey ...!!! :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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