Forum Discussion

ConorN's avatar
ConorN
Occasional Reader
Apr 12, 2026

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 that calculates how many days OVER the acceptable time a delivery is, I want a conditional format that colours that cell on a gradient from 1 to 150, going yellow to deep red. So a quick visual inspect will show which ones are going to be a major problem. The difficulty being the gradienjt scale will have to change depending on the department that deliverable is for.

 

So in Column C I have the department name, this gives Column D data to look up in a config tab, that lets it check how many days is considered late using a basic Vlookup ona small table that is just two columns Dept Name, and acceptable days late number.

=IF(C2="","",VLOOKUP(C2,Configuration!D$4:E$6,2,FALSE))

Then Column H looks at the order date(Col F) and delivery due date (Col G), and checks if the time between is over or under the acceptable range with a simple comparison and show how many days (If any) late the delivery will be.

=IF(G2-F2>D2,(G2-F2)-D2,"")

I want Column H to be conditionally formatted with the mentioned gradient, however the conditions for that gradient will change depending on whats in the Department Column C, some departments consider 14 days over to be acceptable (coloured yellow), some departments see 8 days as a critical issue (Coloured deep red).

So my question is how I apply a different Conditional Formatting Rule to the cell in Col H, depending on the value of Col C. If thats even possible.

I know I can do it with a macro, but I want to try keep this worksheet formula's only if possible as the person using it does not use VBA and wouldn't know how to troubleshoot if something went wrong, whereas I could explain a formula to them. 

 

 

excel
Formulas and Functions

2 Replies

  • Olufemi7's avatar
    Olufemi7
    Iron Contributor
    Apr 13, 2026

    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

    1. Current setup
      Column C = Department
      Column D = acceptable days
      Column H = days late
    2. 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

    https://support.microsoft.com/en-gb/office/use-conditional-formatting-to-highlight-information-in-excel-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Apr 13, 2026

    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.