Forum Discussion

Laurie McDowell's avatar
Laurie McDowell
Copper Contributor
Jun 15, 2017

Formula or function for IF statement based on cell color

I don't know how to code in VBA but am trying to automate an if/then calculation based on cell color. As shown in the picture, if the colors of the cells in column B are the same as those in Column G...
Formulas & Functions
Need Help
  • Kevin Lehrbass's avatar
    Kevin Lehrbass
    Jun 15, 2017

    Step 1 Paste code (found at bottom) into a new module. ALT F11 shortcut should open the code area.
    Step 2 In cell O1 paste formula: =InteriorColor(B1) drag formula down
    Step 3 In cell P1 paste formula: =InteriorColor(G1) drag formula down
    Step 4 In cell L1 paste formula: =IF(O1<>P1,F1+K1,ABS(F1-K1)) drag formula down

     

    Does this work for you Laurie?

    Cheers,

    Kevin

     

    Function InteriorColor(CellColor As Range)
    Application.Volatile
    InteriorColor = CellColor.Interior.ColorIndex
    End Function 

     

David Rich's avatar
David Rich
Copper Contributor

 

Hi Sergei,

 

Thank you for replying. I hope the below makes a little more sense.

 

Below is the criteria I was given and below I have attached a screen shot of the spreadsheet with the relevant columns that my query relates to.

 

  • Make the ‘Status’ column field automatically show the words Active, Dormant or Inactive based on the following conditions:
    • Active- if there were purchases in the current 12 months (third column)
    • Dormant- if there was purchases in previous 12 month period (Second Column) but nothing in Current 12 months period (third column)
    • Inactive- if there was no purchases in second or third column

Note: I'm thinking the answer is utilising a IFS & ISBLANK combined formula, but so far I haven't been able to get this combination to work to encompass all three criteria..if that makes sense. So far I have only been able to get the following IF formula to work but it is not the total solution: =IF(W5=0,("Inactive"),("Active"))

 

  • David Rich's avatar
    David Rich
    Copper Contributor
    Aug 27, 2018

    Hi Sergei, worked a treat - thanks so much.

    • jww76's avatar
      jww76
      Copper Contributor
      Sep 01, 2021

      This is long after the fact but David Rich - it appears your condition for dormant is something like "last 12 months volume is lower than prior year volume"?

       

      If so, you could modify Sergei's formula to:

      =IF(C2>0, "Active",IF(B2>C2, "Dormant", "Inactive"))

      It also seems like 'Inactive' is specifically a matter of both B2 and C2 being 0? If so:

      =IF(C2>0, "Active",IF(B2>C2, "Dormant", (AND(B2=0,C2=0)=TRUE,"Inactive","CHECK")))

      This looks for both to be '0' and then returns a CHECK if it ends up that none of the three conditions are met. Say that B2 = C2 and both are greater than 0.

        

Resources