Forum Discussion

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

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 

     

SergeiBaklan's avatar
SergeiBaklan
MVP
Aug 20, 2018

Hi David,

 

If you use conditional formatting you may apply same formulas as for conditional formatting rules to your another column to return the text.

David Rich's avatar
David Rich
Copper Contributor
Aug 20, 2018

Hi Sergei,

 

I sort of figured that but I can seem to find a formula to achieve the outcome. Do you have a example formula that might work?

 

Thanks, David

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Aug 21, 2018

    David, it depends on which conditional formatting rules you use. If you provide small sample it'll be easier to answer.

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

       

      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"))

       

Resources