Forum Discussion
Select from multiple conditional format rules, based on value of another cell.
I have a spreadsheet tracking due dates for deliverables, depending on what department a deliverable is for, the acceptable time taken from order to delivery varies. On Column H, I have a formula ...
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