+ Reply to Thread
Results 1 to 4 of 4

Multiple variables for conditional formatting

  1. #1
    Registered User
    Join Date
    09-09-2016
    Location
    California, USA
    MS-Off Ver
    Office 2010
    Posts
    2

    Multiple variables for conditional formatting

    I just had a request to change a formula I've had for a while to have a slightly different scope. Basically there are 4 issues I'm tracking and I need the highlighted color of a cell to change based on the number of occurrences for each specific problem. Here's the current formulas I'm using:

    ='Employee 1'!$E$12>4.3
    =IF('Employee 1'!$E$12<4.1,IF('Employee 1'!$E$12>2.1, TRUE, FALSE), TRUE)
    =IF('Employee 1'!$E$12>0.1,IF('Employee 1'!$E$12<2.05, TRUE, FALSE), TRUE)

    The first is highlighted red, 2nd is highlighted orange, 3rd is highlighted green.

    What I need is the cell highlighted red if E12 is greater than or equal to 5, or F12, G12 or H12 are greater than or equal to 3. Orange highlighting if E12 between 2 and 4 or F12, G12 or H12 = 2. Green if any are 1. Red > Orange > Green. Is there any way I can accomplish this or would I need to do a different conditional format for each cell? The other reason I'm asking this is because I have 'employee 1' to 'employee 40' and adding 12 specific lines of conditional formatting to 40 cells seems a bit excessive.

    If anyone can help I would appreciate it!

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

    Re: Multiple variables for conditional formatting

    Red:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Orange:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I'll leave you to gave a go at Green
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    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
    Registered User
    Join Date
    09-09-2016
    Location
    California, USA
    MS-Off Ver
    Office 2010
    Posts
    2

    Re: Multiple variables for conditional formatting

    And that is quite a bit more simple than my initial formula... thank you!!

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

    Re: Multiple variables for conditional formatting

    You're welcome ... and I didn't want to mention that But should be easier to adapt and maintain.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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 Variables
    By h.noor in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-09-2015, 04:15 PM
  2. [SOLVED] Conditional Formatting with variables
    By Aussie1 in forum Excel General
    Replies: 1
    Last Post: 06-04-2014, 05:38 PM
  3. VBA conditional formatting variables
    By charles1239 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-01-2012, 10:22 PM
  4. Conditional Formatting with 2 variables (or 3?)
    By Big1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-08-2012, 04:07 PM
  5. Conditional Formatting - more than 3 variables
    By mc515 in forum Excel General
    Replies: 2
    Last Post: 03-20-2008, 07:36 AM
  6. Conditional formatting w/ > 3 variables
    By Kearse56 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-28-2007, 09:24 AM
  7. [SOLVED] Conditional formatting for more than 3 variables
    By Emile in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-07-2006, 12:30 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