Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 718
  • Last Modified:

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?

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
pcamis
Asked:
pcamis
  • 4
  • 3
  • 2
1 Solution
 
ProfessorJimJamCommented:
please find attached.
Sample.xlsx
0
 
pcamisAuthor 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):

Screencap2.PNGSample2.xlsx
0
 
ProfessorJimJamCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
pcamisAuthor 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!
0
 
Glenn RayExcel VBA DeveloperCommented:
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
 
pcamisAuthor Commented:
Fantastic workaround, Glenn Ray - works beautifully!  Thanks very much!
0
 
ProfessorJimJamCommented:
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
 
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.

-Glenn
0
 
ProfessorJimJamCommented:
Thanks Glenn
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now