Conditional formatting and uniquely identifying cells

jhop15

Hi,

I would like to only apply conditional formatting to cells that are percentages.

My rules currently set any cells that are over 100% as red, and 100% or less as green. Due to the red rule formatting any cell over 1.01 a lot of my data is showing as red, wheras this really needs to be white, there is no cause to flag these numbers.

I know that on Smartsheet you cannot make conditional formatting cell specific, however I thought maybe I could try and uniquely identify the percentage cells to stop the formatting spreading to to the other numerical cells.

I have tried the rule 'identify cells that are over 1.01 AND contain %' however this doesn't work as the % cell is a formula and so the % symbol isn't recognised.

Does anyone have any suggestions as to how I can make this happen? Screenshot attached.

Thanks!

 

 

 

Paul Newcome

You could add a checkbox column and have it checked if the corresponding row is a percentage row. You could then base your Conditional Formatting off of the checkbox column. Unfortunately I haven't been able to (yet) successfully automate the checkbox column based on the presence of a "%", but I'll keep you posted if I figure something out.

jhop15

Thanks Paul.

Do you know what I would need to enter into the 'contains' section of the checked box rule to differentiate between <1.01 and >1.01? 

I still need to be able to highlight the cells as red or green as opposed to formatting the whole % row as one colour.

 

Paul Newcome

You would use the same conditional formatting rules and just "add a condition" to include the checkbox. See below...

jhop15

Thanks so much - that worked perfectly!

 

Problem solved.

Paul Newcome

Awesome sauce! Glad we could help!