?
Solved

Excel Conditional Formatting: Using Color Scale but with Additional Criteria

Posted on 2014-10-22
9
Medium Priority
?
606 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 2000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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 article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

762 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