+ Reply to Thread
Results 1 to 7 of 7

Fill color of cells with conditional formatting for more than 3 criterias

  1. #1
    Registered User
    Join Date
    09-12-2014
    Location
    Norman
    MS-Off Ver
    2010
    Posts
    37

    Fill color of cells with conditional formatting for more than 3 criterias

    Hello All,

    In the attached workSheet2 I want to color rows based on text criteria. Considering Columns A, C and H. For an application with its corresponding severity level, we check the number of cycles in Col H and display specified color in Table in M5:Q13.
    1) If an application has critical severity and number of cycles = 1 then display green, if its >1 then fill red.
    2) Similarly for an application with High Severity and no. of cycles = 1 then display green, if its >1 fill red.
    3) Similarly for an application with Medium Severity and no. of cycles < 2 then display green, if its >2 fill red.
    4) Similarly for an application with Low Severity and no. of cycles < 3 then display green, if its >3 fill red.

    If an application has no entry in the sheet then by default it will fill with green color. Ex: In this worksheet Application - Sony has 0 Low count and color in Q6 is Green.

    I know we can use Conditional Formatting for 3 sets of criteria. So, can you suggest if its fine to use Conditional Formatting or use VBA.
    Last edited by Sai Prashanth; 10-20-2014 at 04:56 PM.

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Conditional Formatting on 3 cells and fill color in 4th cell

    I'm confused since there are duplicate values under application. Are you taking into account multiple severity statuses for a single application? If there was just one application for each, I believe this could be done with conditional formatting relatively easily.

  3. #3
    Registered User
    Join Date
    09-12-2014
    Location
    Norman
    MS-Off Ver
    2010
    Posts
    37

    Re: Conditional Formatting on 3 cells and fill color in 4th cell

    @mcmahobt - We need not worry about those duplicate applications. Consider for example in column A: Apple has 1 critical issue and 2 high issues. So, for Apple with severity Critical number of cycles are 3, means Cell M7 should go Red. Apple has 2 High issues, so even if one of them has cycle value >1 then N7 should go red.

  4. #4
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Conditional Formatting on 3 cells and fill color in 4th cell

    That makes sense, but the question was more for formula and function purposes, since some functions such as MATCH will only stop at the first instance a value appears, which may be wrong for your application.

  5. #5
    Registered User
    Join Date
    09-12-2014
    Location
    Norman
    MS-Off Ver
    2010
    Posts
    37

    Re: Conditional Formatting on 3 cells and fill color in 4th cell

    Ok. So, is there any other way you might possibly suggest me to complete this task?

  6. #6
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Conditional Formatting on 3 cells and fill color in 4th cell

    Is the sample workbook you attached that contains the conditional formatting supposed to be a representation of what you are trying to achieve? Because if so, I'm not following the thought process since Sony shows a Medium severity with 2 cycles in row 2, but Critical severity and 3 cycles in row 6...so which do you want reflected in the conditional formatting?

  7. #7
    Registered User
    Join Date
    09-12-2014
    Location
    Norman
    MS-Off Ver
    2010
    Posts
    37

    Re: Conditional Formatting on 3 cells and fill color in 4th cell

    @mcmahobt: The worksheet I have attached has no conditional formatting. I have manually filled the cells M6:P7 to explain what I am trying to achieve. May be I did not understand your question. As you see Sony shows Medium severity with 2 cycles in row 2, So O6 should be Green, and Critical severity has 3 cycles in row 6 so format M6 with Red.

    Over all task is, Ex: M6 represents Sony with Critical issues. So we look at data for Application name Sony and Severity be Critical and there corresponding number of Cycles. If any cycle value is >1 then format M6 with RED and if it is =1 format M6 with Green.

    If an application has more than one same severity(like Apple has 2 High issues) den look for all corresponding cycles and even if one cycle > 1 den turn red for High in N7.
    Last edited by Sai Prashanth; 10-20-2014 at 04:01 PM.

+ 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: 21
    Last Post: 12-16-2015, 03:04 PM
  2. Replies: 10
    Last Post: 06-19-2013, 05:28 PM
  3. Replies: 3
    Last Post: 08-03-2012, 03:45 AM
  4. Cell Formatting Conditional On Other Cells Fill Color?
    By Kael_Sidhe@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-06-2006, 12:25 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