Conditional Formatting Rules

%3CLINGO-SUB%20id%3D%22lingo-sub-1613923%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Rules%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1613923%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EHi%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EI%20am%20trying%20to%20make%20two%20graded%20color%20scales%20work%20together%20for%20a%20set%20of%20data%20using%20the%20conditional%20formatting%20rules.%20One%20graded%20color%20scale%20is%20for%20values%20between%2010%20and%2020%2C%20with%20the%20second%20graded%20scale%20for%20values%2030-40.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EMy%20issue%20is%20that%20when%20the%2010-20%20graded%20scale%20is%20used%20first%2C%20everything%2020%20and%20above%20is%20the%20same%20color%20and%20the%2030-40%20scale%20is%20not%20applied.%20How%20can%20I%20use%20a%20graded%20color%20scale%20to%20only%20effect%20the%20cells%20that%20have%20a%20value%20within%20the%20range%3F%20%3C%2FFONT%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EThank%20you%20for%20your%20time!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1613923%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1614058%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Rules%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1614058%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F772052%22%20target%3D%22_blank%22%3E%40adamboscoucdenver%3C%2FA%3E%26nbsp%3B%20sounds%20like%20you%20want%20a%20full%20rainbow%20of%20color.%26nbsp%3B%20I%20don't%20believe%20that%20is%20possible%20(would%20love%20to%20hear%20someone%20correct%20me%20because%20that%20would%20be%20really%20interesting).%26nbsp%3B%20That%20said%2C%20I%20assume%20you%20are%20already%20using%203-Color%20Scale%20for%20each%20range%2C%20because%20otherwise%20you%20might%20get%20similar%20results%20by%20using%20that%20instead%20of%202%202-color%20ranges.%26nbsp%3B%20The%202%20other%20options%20for%20you%20are%20then%20using%20a%20lot%20of%20custom%20formula%20based%20conditional%20formatting%20(ugh%20even%20thinking%20of%20that)%20or%20a%20custom%20VBA%20function%20that%20basically%20does%20a%20custom%20conditional%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1615415%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Rules%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615415%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F772052%22%20target%3D%22_blank%22%3E%40adamboscoucdenver%3C%2FA%3E%26nbsp%3BPerhaps%20the%20attached%20workbook%20holds%20an%20acceptable%20solution%20for%20you.%20It%20uses%20one%203-color%20scale%20for%20numbers%20from%2010%20to%2040%2C%20after%20explicitly%20formatting%20numbers%20less%20than%2010%2C%20between%2020%20and%2030%2C%20and%20greater%20than%2040%20with%20%22no%20fill%22.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-08-27%20at%2009.14.17.png%22%20style%3D%22width%3A%2064px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214931i9DDB98EC11904D66%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screenshot%202020-08-27%20at%2009.14.17.png%22%20alt%3D%22Screenshot%202020-08-27%20at%2009.14.17.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi

I am trying to make two graded color scales work together for a set of data using the conditional formatting rules. One graded color scale is for values between 10 and 20, with the second graded scale for values 30-40.

My issue is that when the 10-20 graded scale is used first, everything 20 and above is the same color and the 30-40 scale is not applied. How can I use a graded color scale to only effect the cells that have a value within the range?

 

Thank you for your time!

2 Replies

@adamboscoucdenver  sounds like you want a full rainbow of color.  I don't believe that is possible (would love to hear someone correct me because that would be really interesting).  That said, I assume you are already using 3-Color Scale for each range, because otherwise you might get similar results by using that instead of 2 2-color ranges.  The 2 other options for you are then using a lot of custom formula based conditional formatting (ugh even thinking of that) or a custom VBA function that basically does a custom conditional formatting.

 

@adamboscoucdenver Perhaps the attached workbook holds an acceptable solution for you. It uses one 3-color scale for numbers from 10 to 40, after explicitly formatting numbers less than 10, between 20 and 30, and greater than 40 with "no fill".

Screenshot 2020-08-27 at 09.14.17.png