Forum Discussion
Laurie McDowell
Jun 15, 2017Copper Contributor
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...
- 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 downDoes this work for you Laurie?
Cheers,
Kevin
Function InteriorColor(CellColor As Range)
Application.Volatile
InteriorColor = CellColor.Interior.ColorIndex
End Function
David Rich
Aug 20, 2018Copper Contributor
I'm having a similar problem, let's say on my spreadsheet in column W on a range of W5:W268 has values showing as different colours based on conditional formatting formulas. Now what I want to do in another column is based on the colour in the range W5:W268 I want a word to appear. So if the colour is green, I want the text in the adjacent column to say Active, if the colour is red, Inactive & if the colour orange, Domant. I can't find anywhere online showing me how to achieve this. Thanks heaps
- SergeiBaklanAug 20, 2018MVP
Hi David,
If you use conditional formatting you may apply same formulas as for conditional formatting rules to your another column to return the text.
- David RichAug 20, 2018Copper Contributor
Hi Sergei,
I sort of figured that but I can seem to find a formula to achieve the outcome. Do you have a example formula that might work?
Thanks, David
- SergeiBaklanAug 21, 2018MVP
David, it depends on which conditional formatting rules you use. If you provide small sample it'll be easier to answer.