+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting applied via VBA - multiple conditions

  1. #1
    Registered User
    Join Date
    07-20-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    43

    Conditional Formatting applied via VBA - multiple conditions

    I have two columns of figures (col M and N) - they are updated several times a second from outside XL.

    I want to compare every pair of cells in the 2 columns - so M5 with N5 and M8 with N8 etc.

    I want to color the cell with the largest value (of the 2) Pink and the cell with the lower value Blue.

    So I will have a patchwork of cells in the two columns, all colored either Pink or Blue.

    I have tried with code like this;

    Please Login or Register  to view this content.
    This does not work correctly - I have put the value of 2 in all the M column cells and 10 in all the N column cells to check and even if I run just the first block - it does not color correctly. If someone can give me some code that works I am sure I can adjust it suit exactly.

    Thanks for reading.

    Tobias

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

    Re: Conditional Formatting applied via VBA - multiple conditions

    Please Login or Register  to view this content.
    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 leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Conditional Formatting applied via VBA - multiple conditions

    Nice, AlphaFrog, but do you really want to format entire columns? That's gonna extend the used range to a million rows, increasing workbook size dramatically. I would assume it also slows sheet calculation.

  4. #4
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    863

    Re: Conditional Formatting applied via VBA - multiple conditions

    This is possibly simpler, but could use some work. Why do we need min/max as well if just comparing 2 numbers?

    Please Login or Register  to view this content.
    Also, should this done in the actual sheet using Worksheet_SelectionChange so that it updates automatically with each change?

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

    Re: Conditional Formatting applied via VBA - multiple conditions

    Quote Originally Posted by leelnich View Post
    Nice, AlphaFrog, but do you really want to format entire columns? That's gonna extend the used range to a million rows, increasing workbook size dramatically. I would assume it also slows sheet calculation.
    It doesn't extend the used range. Excel has implicit limits to work within the used range for formulas and functions that reference entire columns and rows.

    Add this after the conditional formatting of the columns to verify.

    ActiveSheet.UsedRange.Select


    Here's a good link that tests the perfomance;
    Excel Full Column References and Used Range: Good Idea or Bad Idea?
    Last edited by AlphaFrog; 05-23-2017 at 03:46 PM.

  6. #6
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Conditional Formatting applied via VBA - multiple conditions

    I stand corrected.

  7. #7
    Registered User
    Join Date
    07-20-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Conditional Formatting applied via VBA - multiple conditions

    lphaFrog - Man, that was great info to get ! Thanks a million, it really put me on the right track.

    Am I right in thinking that I can only have 3 sets of CF, IE one more than your code shows ?

    Thanks for taking the time to help me, I appreciate it. Tobias

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

    Re: Conditional Formatting applied via VBA - multiple conditions

    Quote Originally Posted by Ramses505 View Post
    lphaFrog - Man, that was great info to get ! Thanks a million, it really put me on the right track.

    Am I right in thinking that I can only have 3 sets of CF, IE one more than your code shows ?

    Thanks for taking the time to help me, I appreciate it. Tobias
    You're welcome.

    With Excel 2003 or earlier, you are limited to three Conditional Format rules for a given cell.

    In Excel 2007 and later, I don't know what the limit is, but it's probably much more than you need. I've seen a few people use dozens.

+ 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. Conditional Formatting Rule with Formula Applied to Multiple Cells
    By Fabi1963 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-31-2015, 06:54 AM
  2. [SOLVED] conditional formatting -multiple conditions
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-31-2013, 05:51 PM
  3. Conditional Formatting with multiple conditions
    By naaz in forum Excel General
    Replies: 5
    Last Post: 06-05-2012, 03:21 AM
  4. Conditional Formatting using VBA for multiple conditions
    By stephboucher in forum Excel General
    Replies: 0
    Last Post: 01-18-2011, 10:13 AM
  5. Conditional Formatting - Multiple Conditions
    By criblo in forum Excel General
    Replies: 1
    Last Post: 12-16-2010, 06:40 PM
  6. Multiple Conditions for Conditional Formatting
    By mrlevcik in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2008, 12:55 PM
  7. Multiple Conditions for Conditional Formatting
    By horton in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-03-2008, 02:58 AM

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