+ Reply to Thread
Results 1 to 13 of 13

How to loop through sheets in worksheet and apply conditional formatting

  1. #1
    Forum Contributor
    Join Date
    03-01-2019
    Location
    New York, New York
    MS-Off Ver
    Office 2016 for Mac
    Posts
    125

    How to loop through sheets in worksheet and apply conditional formatting

    Hi again! First of all, thank you to everyone on this forum. My VBA skills have improved a ton since I joined.

    I need to apply conditional formatting to the same range on 8-10 different worksheets with the workbook. Since the highlighting is based on comparing the value to a different cell for each worksheet, I am thinking I will have to code out each worksheets format conditions. You will see that all that really changes is the cell references under "define the rule or each conditional format" from $D$4 to $D$5 (there are 8-10 different cell references, one for each sheet). Is there a way to loop through the 8-10 worksheets, changing the format conditions to reflect the changing comparison value cell reference?

    Code for Worksheet #1

    Please Login or Register  to view this content.
    Code for Worksheet #2

    Please Login or Register  to view this content.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,250

    Re: How to loop through sheets in worksheet and apply conditional formatting

    Assuming that it is selective sheets rather than all sheets, you could have two arrays, one for the sheets, or sheet names, and one for the relevant cell addresses.

    Then use
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    03-01-2019
    Location
    New York, New York
    MS-Off Ver
    Office 2016 for Mac
    Posts
    125

    Re: How to loop through sheets in worksheet and apply conditional formatting

    Thanks, Trevor! Since I am still relatively new to this...is this the code change you are suggesting?

    Please Login or Register  to view this content.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,250

    Re: How to loop through sheets in worksheet and apply conditional formatting

    Yes, but rngArray(i) is NOT in quotes. That's the principle anyway, but you might need to tinker with it.

  5. #5
    Forum Contributor
    Join Date
    03-01-2019
    Location
    New York, New York
    MS-Off Ver
    Office 2016 for Mac
    Posts
    125

    Re: How to loop through sheets in worksheet and apply conditional formatting

    OK, I'll try it out tomorrow and see what I can do. Thank you for the idea!

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,250

    Re: How to loop through sheets in worksheet and apply conditional formatting

    You're welcome.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,250

    Re: How to loop through sheets in worksheet and apply conditional formatting

    I think it should look something like:

    Please Login or Register  to view this content.
    But you might have to tinker with the last condition ... or have a third array.

  8. #8
    Forum Contributor
    Join Date
    03-01-2019
    Location
    New York, New York
    MS-Off Ver
    Office 2016 for Mac
    Posts
    125

    Re: How to loop through sheets in worksheet and apply conditional formatting

    I am working on a similar but more simple conditional formatting (for another project) as a way to get the hang of coding this. But my code is only formatting the first sheet in the array. Any tips?

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    03-01-2019
    Location
    New York, New York
    MS-Off Ver
    Office 2016 for Mac
    Posts
    125

    Re: How to loop through sheets in worksheet and apply conditional formatting

    Realized I am probably missing some declarations, so I added:

    Please Login or Register  to view this content.
    But this still did not fix things...

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,250

    Re: How to loop through sheets in worksheet and apply conditional formatting

    Ok, I think it needs to look like this:

    Please Login or Register  to view this content.
    However, I can't test any of this a) because Iím on an iPad and b) because I have nothing to test it on

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,112

    Re: How to loop through sheets in worksheet and apply conditional formatting

    Please Login or Register  to view this content.
    Your array has three elements, therefore the line

    For i = LBound(shArray) To UBound(shArray)

    will loop three times on the active sheet. It does not select any other sheet.
    Last edited by protonLeah; 05-17-2019 at 05:43 PM.
    Ben Van Johnson

  12. #12
    Forum Contributor
    Join Date
    03-01-2019
    Location
    New York, New York
    MS-Off Ver
    Office 2016 for Mac
    Posts
    125

    Re: How to loop through sheets in worksheet and apply conditional formatting

    Thanks, Ben! That code works. I appreciate the help.

    Trevor, thank you for your suggestions as well!

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,250

    Re: How to loop through sheets in worksheet and apply conditional formatting

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Loop through worksheets and apply conditional formatting
    By beenbee in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-02-2016, 07:20 AM
  2. [SOLVED] How to apply conditional formatting across sheets
    By James__S in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-23-2015, 08:14 PM
  3. [SOLVED] Add border & apply conditional formatting to sheets based on cell value
    By amar05 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-14-2013, 11:02 AM
  4. Loop to Match Criteria on Two Sheets and Apply Conditional Format
    By flindy87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-08-2013, 04:06 AM
  5. VBA to Loop through list of sheets and apply macro
    By hungryhobo in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 05-08-2013, 05:54 PM
  6. [SOLVED] Macro to apply Conditional Formatting to a range in every worksheet
    By Floydlevedale in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-15-2013, 01:53 AM
  7. [SOLVED] Loop and apply formatting
    By apunc1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-29-2013, 12:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1