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 

     

Sophie1250's avatar
Sophie1250
Copper Contributor
Mar 05, 2021

Laurie McDowell 

Hopefully people are still replying to this thread!

I could really do with help here if anyone could help,

I've been asked to make it so that depending on the colour for the patient, then the date of the next review will auto populate based on the date of the last review. so if Red  would be date of last review plus 7 days, if amber would be last review plus 14 days and if green would be last review plus 1 month. 

I've tried to apply all that is offered in this thread but am well out of my depth!

 

 

Resources