+ Reply to Thread
Results 1 to 7 of 7

Need to apply conditional formatting to large range of cells

  1. #1
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Need to apply conditional formatting to large range of cells

    Hello everyone,

    I have a conditional format that I need applied to almost 120 rows.

    I recorded the conditional format as i set it up.

    Please Login or Register  to view this content.
    How can I apply this exact same conditional format to all the rows I need?

    Wouldn't this need to me some kind of loop?

    I have no clue how to change the range each time the condition is applied. Basically I need the condition applied to a row, move down to the next row and apply the same condition. Changing the "$F$3="No """ to F4, F5, F6, etc...

    Sample attached.

    Thank you in advance.

    Justin
    Attached Files Attached Files
    Last edited by Justair07; 08-31-2016 at 10:45 AM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,075

    Re: Need to apply conditional formatting to large range of cells

    • Select the entire range in your macro.
    • Apply the CF formula using a relative reference for the row e.g.: Formula1:= "=$F3=""No """
    • Excel will automatically adjust the formulas for each row.
    Last edited by AlphaFrog; 08-31-2016 at 10:50 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,314

    Re: Need to apply conditional formatting to large range of cells

    You need to change the range to include the rows you want and take out the absolute row reference.
    If your range is B3:K30 (for example), then use this:
    Please Login or Register  to view this content.
    Note that each time you run this code, it will create a new version of the CF rule, so you may also want to add this line after the range selection:
    Please Login or Register  to view this content.
    Hope that helps.

    Out of curiosity, is there a reason why you need this to be done in VBA rather than just using CF directly?
    Regards,
    Aardigspook

    I've just moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  4. #4
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Need to apply conditional formatting to large range of cells

    When I try to do this directly in CF it bugs up. Only some of the cells are formatted. Apparently this is a know issue in excel. If I try to apply the rule to each individual row it will take me forever and I only have 30 minutes to get this completed.

    Correction,

    I'm just an idiot whos rushing too much lol. It worked fine when I applied the rules as AlphaFrog directed.

    Thanks a ton!!
    Last edited by Justair07; 08-31-2016 at 11:12 AM.

  5. #5
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,314

    Re: Need to apply conditional formatting to large range of cells

    Okay - I do sometimes get the issue of CF applying incorrectly, though the issue I've seen is quite easy to fix. However, VBA works as well - have you tried the suggestions above (mine and AlphaFrog's are essentially the same - expand the range and take out the $ before the 3 in $F$3 so it's $F3)?

  6. #6
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Need to apply conditional formatting to large range of cells

    Quote Originally Posted by Aardigspook View Post
    Okay - I do sometimes get the issue of CF applying incorrectly, though the issue I've seen is quite easy to fix. However, VBA works as well - have you tried the suggestions above (mine and AlphaFrog's are essentially the same - expand the range and take out the $ before the 3 in $F$3 so it's $F3)?
    Yes, worked perfect!! Thanks again!!

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,314

    Re: Need to apply conditional formatting to large range of cells

    You're welcome - thanks for the feedback and the rep.
    Now that your problem's solved, please mark the thread as Solved so others know there's an answer here (see my sig for instructions). Thanks.

+ 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] 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
  2. Conditional formatting if in range of values: apply to column
    By marcopietro in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-19-2014, 02:34 PM
  3. Apply Conditional Formatting to Relative Range
    By wotaj in forum Excel General
    Replies: 1
    Last Post: 09-18-2014, 12:44 PM
  4. Replies: 3
    Last Post: 06-24-2014, 11:04 AM
  5. Replies: 3
    Last Post: 08-13-2013, 09:44 AM
  6. [SOLVED] Conditional Formatting Apply to Range
    By daved2424 in forum Excel General
    Replies: 3
    Last Post: 09-27-2012, 08:35 AM
  7. Conditional Formatting won't apply to Entire Range
    By DoriBeE in forum Excel General
    Replies: 8
    Last Post: 05-11-2011, 03:55 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