Excel Conditional Formatting: Using Color Scale but with Additional Criteria

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?

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

Dan CarpIT DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Professor JMicrosoft Excel ExpertCommented:
please find attached.
Dan CarpIT DirectorAuthor Commented:
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):

Professor JMicrosoft Excel ExpertCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dan CarpIT DirectorAuthor Commented:
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!
Glenn RayExcel VBA DeveloperCommented:

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dan CarpIT DirectorAuthor Commented:
Fantastic workaround, Glenn Ray - works beautifully!  Thanks very much!
Professor JMicrosoft Excel ExpertCommented:

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)
Glenn RayExcel VBA DeveloperCommented:
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.

Professor JMicrosoft Excel ExpertCommented:
Thanks Glenn
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.