Forum Discussion

JenSmith's avatar
JenSmith
Copper Contributor
Jun 02, 2025
Solved

Excel formula to auto format cell based on value and phrase in second cell

Hi All, 

 

I have a formula query that has stumped me! 

I have a form that records various pieces of data and in one section it asks for payment terms. I have a simple conditional format in place at the moment that just highlights a certain colour based on the value entered into the payment terms field (value 1-30 cells fills green, value over 30 cell fills red). 

I would like to adjust this to conditional format the cell based on the value in cell 1 and the phrase in cell 2.

E.g.:  If value in Cell1 is between 1 - 30, AND the phrase in Cell2 = "Days NET" 

 

Cell1 is a free text cell that is restricted via data validation to a numerical value of no more than 3 digits. 
Cell2 is a drop down list selection only consisting of 3 options (Days NET being one of them)

 

I can put each of the 2 elements in singularly but for some reason can't work out how to join the 2 elements together in a formula to make the conditional formatting look at both bits of info in the 2 cells to decide how to format Cell1 (although i am expecting the answer is in my brain somewhere and will be one of those 'Doh!' moments when it's pointed out to me haha). 

 

Thanks in advance for any help. 

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jun 02, 2025

    Select Cell 1.
    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Use a formula to determine which cells to format'.
    Enter the formula

    =AND(Cell1>=1, Cell1<=30, Cell2="Days NET")

    You have to replace Cell1 and Cell2 with the correct cell references of course.
    Click Format...
    Activate the Fill tab.
    Select green as fill color.
    Click OK, then click OK again.

2 Replies

Sort By
  • JenSmith's avatar
    JenSmith
    Copper Contributor
    Jun 02, 2025

    Ah fantastic! Thank you - this is perfect. I was trying to put it into an IF formula which explains why i couldn't work it out. I'm not as familiar with AND formulas so hadn't thought of it. 

     

    Thanks so much! 😃

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 02, 2025

    Select Cell 1.
    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Use a formula to determine which cells to format'.
    Enter the formula

    =AND(Cell1>=1, Cell1<=30, Cell2="Days NET")

    You have to replace Cell1 and Cell2 with the correct cell references of course.
    Click Format...
    Activate the Fill tab.
    Select green as fill color.
    Click OK, then click OK again.

Resources