+ Reply to Thread
Results 1 to 5 of 5

Conditional Formattng

  1. #1
    Registered User
    Join Date
    03-25-2021
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    13

    Conditional Formattng

    I have a spreadsheet that I need it to use a 2 color from white to red conditional format. I have two different conditions I need it to meet.

    For Example,

    If cell A1 says P2, then check cell B1 to see where it falls in the table below. I need the color to get closer to red as the number in cell B1 gets closer to meeting SLA.

    Priority SLA
    P1 12
    P2 36
    P3 400

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,500

    Re: Conditional Formattng

    I assume that this won't be related to a single cell but conditional formatting on a column of cells. Unfortunately, conditional formatting color scales do not allow relative references. I tried playing around with VLOOKUP in the formula to select a maximum value but Excel wasn't having it. Maybe one of the other gurus has a solution. I'm thinking you'd need VBA where 3 different color scales are defined and Excel formats accordingly.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    03-25-2021
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Conditional Formattng

    Correct, it will be for a whole column.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-25-2021
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Conditional Formattng

    Please any help is appreciated.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: Conditional Formattng

    This is based on the formatting in shown column P.
    This proposal employs seven helper columns which may be moved and/or hidden for aesthetic purposes.
    The first three helper columns are populated using: =IF(F$1=$E2,$C2-INDEX($O$2:$O$4,MATCH($E2,$N$2:$N$4,0)),"")
    The next three helper columns are populated using: =IF(F2="","",RANK.EQ(F2,F$2:F$11,0))
    The final helper column is populated using: =SUM(I2:K2)
    There are four conditional formatting rules each of which is similar to: =L2=1 which will fill the cell bright red
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 6
    Last Post: 02-15-2018, 11:54 PM
  2. Macro to set up conditional message based on results of conditional formatting
    By paysola in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-04-2016, 12:38 AM
  3. [SOLVED] Conditional Statement for Negative Numbers inside a Conditional Formula
    By kuzniak in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 12-18-2015, 11:17 PM
  4. Replies: 2
    Last Post: 12-05-2014, 07:15 AM
  5. Conditional Formattng
    By JOONA in forum Excel General
    Replies: 5
    Last Post: 08-06-2014, 05:44 AM
  6. Replies: 3
    Last Post: 05-15-2012, 04:13 PM
  7. Conditional Formatting Code - Deletes Wrong Conditional Format
    By RSpecianJr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2010, 10:53 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