Forum Discussion

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

Brass Contributor

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

AnnieTran

Copper Contributor

Feb 24, 2024

Kevin Lehrbass

Hi Kevin, thank you for your code. It works magic for me. However, can I check if you run into any issue with the file size? I used InteriorColor on an array of 5x80 cells (so 400 executions of the function). The file size changed from 110kb to 45Mb.

I’m using M365 if it matters.

Thank you