Forum Discussion

Simes888's avatar
Simes888
Copper Contributor
Feb 06, 2023

Excel conditional formatting formula not working as expected

I have a column in Excel that contains dates. The column is G, and the data starts on row 2. I have a conditional formatting rule which is supposed to change the colour of each cell in that column, i...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Feb 06, 2023

Simes888 

Select the range starting in G2.

Delete the existing conditional formatting rule, if any.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop down set to 'Cell Value'.

Select 'less than' from the second drop down.

Enter the formula =TODAY()-30 in the box next to it.

Click Format...

Activate the Fill tab.

Select a color.

Click OK, then click OK again.

sleibo's avatar
sleibo
Copper Contributor
Dec 02, 2023
I have a column to which I am trying to apply conditional formatting. The contents are from a =if formula [more specifically:(=IF(D2="H",IF(Q2<3,1,IF(AND(Q2<4,Q2>2),3,IF(AND(Q2<5,Q2>3),4,6))),(IF(D2="S",(IF(Q2<2,3,IF(AND(Q2<4,Q2>1),6,8))),"none")))]. I can not get conditional formatting to work. I have ensured that contents are formatted as general. I have clicked "Text to Columns". I note that if I simply enter the value of the if statement in an adjacent column, that column I can conditionally format normally. Please assist. Thank you.
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 03, 2023

    sleibo 

    Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

    • sleibo's avatar
      sleibo
      Copper Contributor
      Dec 03, 2023

      HansVogelaar 

      Thank you Hans.  Please see:

      https://www.dropbox.com/scl/fo/ebou2bzznsluxesftn5c2/h?rlkey=mg2ehx55asxqccrgggizj7q5v&dl=0

       

      Column R contains  the calculated field that I would like to conditionally format.  I have copied the values from column R to column S and applied conditional formatting to both R and S.  It works as intended in column S, but not in column R.

       

      Note that the basic spreadsheet was downloaded from our payroll provider, so columns A-O contain some weird formatting.

      PS I don't see a way to attach files to this forum directly, is that correct?

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Dec 03, 2023

        sleibo 

        Most users cannot attach files in this forum; some can but I don't know the criteria.

        Your formula switches from returning number values such as 6 or 8 in row 2 to returning text values such as "6" or "8" in row 3 and below; that's why the conditional formatting doesn't work.

        You can simplify the formula in R2 to

        =IF(D2="H",IF(Q2<3,1,IF(Q2>4,6,Q2)),IF(D2="S",IF(Q2<2,3,IF(Q2>3,8,6)),"none"))

        then fill down.

Resources