Forum Discussion
Select from multiple conditional format rules, based on value of another cell.
AFAIK the simple answer is no because those built in gradient conditional formatting do not allow formulas and custom formulas don't give the option to do a gradient of fill colors.
A possible workaround is to create a helper column that converts the days late to a % days late based on the department acceptable range and use that for your conditional formatting.
HelloConorN,
You can’t make a single color scale change per row based on another cell in Microsoft Excel.
Use a helper column to convert values into a comparable scale, then apply one color scale.
Steps
- Current setup
Column C = Department
Column D = acceptable days
Column H = days late - Store department thresholds in your config table
Add a “critical days” value per department
Example
Department | acceptable days | critical days
Use a lookup to return the critical value per row
3. Add helper column K
Formula in K2
=IF(H2="","",H2/E2)
E2 is the critical days value looked up from the config table
4. Apply conditional formatting
Select K2:K1000
Apply a color scale
Minimum = 0 (yellow)
Maximum = 1 (red)
5. Hide column K if needed
Result
Each row is scaled using its department’s own threshold from the config table
Docs