Solved

Excel Conditional Formatting: Using Color Scale but with Additional Criteria

Posted on 2014-10-22
9
563 Views
Last Modified: 2014-10-23
I have a large spreadsheet (consolidated for this example) in which I want to use conditional formatting to color rows of data, but ONLY rows with an "s" in the "Key" column.  Is it possible to set the range for the entire spreadsheet so that I can easily add affected rows later, or I do just need to manually apply the formatting to each of those (currently highlighted) rows?

Screencap.PNG
The other way I've gotten around the issue is by creating a set of custom conditional formatting rules like the following, but it has made the spreadsheet cumbersome to edit:

=AND($B1="s",A1>=60,A1<600) --> format background dark red
=AND($B1="s",A1>=50,A1<60) --> format background red
=AND($B1="s",A1>=40,A1<50) --> format light red
etc.

Thanks!
Sample.xlsx
0
Comment
Question by:pcamis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40396964
please find attached.
Sample.xlsx
0
 

Author Comment

by:pcamis
ID: 40396989
Thanks ProfessorJimJam.  What you provided does read whether an "s" is in the Key column, but I'm trying to implement the three color range feature of conditional formatting to show a range of color.  Visually, it should look like this (spreadsheet attached):

Screencap2.PNGSample2.xlsx
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40397016
here you go.

in accordance to condition specified in your earlier post and the colored sheet you attached in the latest post. . please find attached file.

=AND($B1="s",A1>=60,A1<600) --> format background dark red
=AND($B1="s",A1>=50,A1<60) --> format background red
=AND($B1="s",A1>=40,A1<50) --> format light red
Sample2.xlsx
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pcamis
ID: 40397045
Sorry - I must still not be asking clearly.  I have already implemented a solution like what you provided above... it functions properly but because of the size of the spreadsheet and the number of rules necessary for the level of color detail that is being requested, I am hoping to use the built-in color scale option...

Conditional Formatting --> New Rule --> Format all cells based on their values --> Format Style (3 color scale)

This gives me the appearance I want, but I can't figure out how to use that rule with the additional condition that it only apply to "s" rows.  That make more sense?

Thanks again!
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40398123
pcamis,

You can't define a criteria like you're suggesting with a graded color scale conditional format.   While a dynamic named range might solve this problem, I don't think it's possible to create such a range (although I could do it manually).

However, there is a solution that involves a bit of trickery.  You create a second conditional format rule that tests for a non-"s" value in column B and give that rule precidence.  While both rules would be applied to the same overall range (that is, columns C:K for as many rows as needed), the non-s rule would prevent the 3-color rule from occurring except where the value in column B is "s".
set up of 3-color conditional format
I've attached a modified file for you to see.

Regards,
-Glenn
EE-Sample2.xlsx
0
 

Author Closing Comment

by:pcamis
ID: 40399289
Fantastic workaround, Glenn Ray - works beautifully!  Thanks very much!
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40399332
Glenn,

i am amazed with your solution. thank you. i learned something new today.

just one question, why do you have these named ranges in the workbook. i did not see any of these names used in condtional formatting.  my question is are these named ranges somehow play a role in your condtional formatting? or perhaps you just created them and did not use them?  i want to know if these named ranges play any role in your solution of condtional formatting?

Name      Refers To      Type
S_Lines      =Sheet1!$C$2:$K$2,Sheet1!$C$6:$K$6
Stest      =OFFSET(Sheet1!$B$1,MATCH("s",Sheet1!XET1:XET19,0),1,1,9)
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40399778
Those were some test ranges I had been working on in an attempt to see if I could create a dynamic range that met the conditional formatting criteria.  They're not actively used

S_Lines - just a manually-designated area covering the two "s" rows
Stest - picks up the first occurrence of an "s" in column B and selected the related columns.  The XET1:XET19 section is mucked up; it should be $B$1:B$B19.  I forgot to use absolute cell referencing when I first wrote it.

-Glenn
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40400007
Thanks Glenn
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question