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 

     

Kevin Lehrbass's avatar
Kevin Lehrbass
Brass Contributor

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 

 

Kevin Lehrbass's avatar
Kevin Lehrbass
Brass Contributor
Jun 15, 2017

You'll have to save your file as ".xlsm" (m for macro).

  • palle1530's avatar
    palle1530
    Copper Contributor
    Aug 07, 2019

    Kevin Lehrbass 

     

    Mabye you can help me 🙂

     

    I have a value in cell J18 that i would like to return to cell C23 - IF - cell C19 has the color red

     

    Is that possible? 🙂

     

     

Resources