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

Dee2021

Copper Contributor

Jun 30, 2021

Kevin Lehrbass

Hello I have a similar issue.

I have 3 different cells that are colored. Can I create a formula to display a number value in a separate cell based on the colors of those cells.?

If cell B2= color green, then value in cell A2=3.

If cell C2= color green, then value in A2=2.

If cell D2= color green, then value in A2= 1

Kevin Lehrbass
Brass Contributor
Jul 01, 2021
Hi Dee2021
For this I think you would need a vba solution.
vba can obtain the background color of a cell and use it in logic (formula or more vba).
Cheers,
Kevin