• Blog
  • Excel Downloads
    • Audit Tickmark Toolbar
  • Courses
  • Contact
  • Checkout

Excel Zoom

...because it's more than just a calculator


Using a For Loop

April 11, 2009 by Mark 1 Comment

 “For” loop is used in a macro whenever you have an action that needs to be performed a set number of times. For example, if you want to change the fill color of the first 20 cells in a list, you could have the macro select the range and simply fill as desired. But what if you wanted to only fill the cells if they were blank, for example? Telling the macro to select the whole range and fill it wouldn’t work.

This is where a “For” loop is useful. You can have the macro to go down your list, and determine if the cell is blank. If it is, then have the macro fill the cell with a color, otherwise go to the next cell, until it has run through the whole list.

The following code will do exactly what was mentioned above.

You must be logged in and have an active membership to view this content. Please register or login to continue.

  • The first line identifies the range of rows to be evaluated. It also tells the macro, that if ‘x’ is less than 20, keep on going.
  • The next line tells the macro to select the cell in column 1.
  • The ‘If’ section determines if the selected cell is blank, and if it is, it will fill it with a color (in this case 65535=yellow), and if it isn’t blank then just move on and evaluate the rest of the cells until the end of the range.

Filed Under: Macros, Tips Tagged With: Macros, Tips

Looking For More Help?

Contact us with any specific questions or feedback. We love to hear from you!

Want to level up your career? See our amazing Certificated Excel Courses and be the office Hero with your new found knowledge!

Looking for the Excel Audit Tickmark Toolbar? click here, NOW ONLY $25!

Subscribe to our mailing list
  • Facebook
  • Twitter

Search this site…

Categories

  • Macros (34)
  • Tips (67)
  • Uncategorized (23)



Copyright © 2023 · Magazine Pro Theme on Genesis Framework · WordPress · Log in

Login Form

Lost your password?