+ Reply to Thread
Results 1 to 7 of 7

How to loop through sheets with Range(Cells and apply conditional formatting

  1. #1
    Registered User
    Join Date
    04-10-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    Office365
    Posts
    17

    How to loop through sheets with Range(Cells and apply conditional formatting

    Dear all,

    I'm a bit stuck. Hoping someone can help.

    As part of a calendar I'm making I'm trying to loop through the sheets and apply some condtional formatting.

    Every sheet is a month, but I can only seem to apply the conditional formatting to highlight bank holidays and weekends to the first sheet "January".

    Looping through the sheets and I can add other values without issue.

    Please Login or Register  to view this content.
    It seem you can't use ActiveWorkbook.Worksheets(c) before Range(Cells... , but it's a problem for me because I'm using Cells to find the last rows (LastRow) and last columns (LastCol)

    How can I add the below to either the loop or to all the sheets in the workbook.

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    Thanks all.
    Last edited by RuairiO; 05-16-2020 at 01:55 PM.

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

    Re: How to loop through sheets with Range(Cells and apply conditional formatting

    You have numerous lines such as:
    Range(Cells(5, 3), Cells(LastRow + 2, LastCol)).FormatConditions.Add
    and
    With Range(Cells(5, 3), Cells(...
    that, since they are not qualified (not inside a With..End With block), apply to the active sheet, whatever that may be.


    Your code should look something like:
    Please Login or Register  to view this content.
    Last edited by protonLeah; 05-16-2020 at 04:15 PM.
    Ben Van Johnson

  3. #3
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    718

    Re: How to loop through sheets with Range(Cells and apply conditional formatting

    Hey RuairiO,

    Can you post your full code or better yet attach sample file as it is difficult to understand where you're struggling.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,509

    Re: How to loop through sheets with Range(Cells and apply conditional formatting

    Possibly...
    Please Login or Register  to view this content.
    ____________________________________________________________
    If I've been helpful, let me know. If I haven't, let me know that too.

  5. #5
    Registered User
    Join Date
    04-10-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    Office365
    Posts
    17

    Re: How to loop through sheets with Range(Cells and apply conditional formatting

    I think I understand. Thanks!

  6. #6
    Registered User
    Join Date
    04-10-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    Office365
    Posts
    17

    Re: How to loop through sheets with Range(Cells and apply conditional formatting

    Thanks a lot both. This worked. Like ProtonLeah mentioned I wasn't doing the With..End With blocks correctly. It's a bit tricky I find...

    Thanks also for the .Index tip instead using the c counter.

    Much appreciated!

  7. #7
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,509

    Re: How to loop through sheets with Range(Cells and apply conditional formatting

    Glad it helped and thank you!

+ 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] How to loop through sheets in worksheet and apply conditional formatting
    By Ianmacros in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-20-2019, 02:00 PM
  2. Need to apply conditional formatting to large range of cells
    By Justair07 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-01-2016, 12:59 AM
  3. [SOLVED] VB code to apply Conditional formatting to a range of cells
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-10-2016, 11:49 AM
  4. [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
  5. [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
  6. 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
  7. [SOLVED] Loop and apply conditional formats through a range row by row
    By BeachRock in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-11-2012, 11:57 PM

Tags for this Thread

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