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

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

JustinPeterson

Copper Contributor

Dec 03, 2019

Kevin LehrbassThanks for the great function and reminding me about custom functions. Huge help!

Forum Discussion

Formula or function for IF statement based on cell color

Share

Resources