Formula or function for IF statement based on cell color

[Laurie McDowell]

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 across the row, I want to subtract the values in columns F and K in the same row to return the absolute value of the subtraction in column L. If the colors of the cells are different, I want to add the values in columns F and K and return the value in column L.

I will have multiple tables of varying numbers of rows where I need to perform this operation. Any help is greatly appreciated.

[#2 · Accepted solution]

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 

[#3]

You'll have to save your file as ".xlsm" (m for macro).

[#4]

Kevin, this worked like a charm. You are the hero of the day!! Thanks ever so much!

[#5]

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

[#6]

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.

[#7]

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

[#8]

David, it depends on which conditional formatting rules you use. If you provide small sample it'll be easier to answer.

[#9]

Hi Sergei,

Thank you for replying. I hope the below makes a little more sense.

Below is the criteria I was given and below I have attached a screen shot of the spreadsheet with the relevant columns that my query relates to.

• Make the ‘Status’ column field automatically show the words Active, Dormant or Inactive based on the following conditions:
• Active- if there were purchases in the current 12 months (third column)
• Dormant- if there was purchases in previous 12 month period (Second Column) but nothing in Current 12 months period (third column)
• Inactive- if there was no purchases in second or third column

Note: I'm thinking the answer is utilising a IFS & ISBLANK combined formula, but so far I haven't been able to get this combination to work to encompass all three criteria..if that makes sense. So far I have only been able to get the following IF formula to work but it is not the total solution: =IF(W5=0,("Inactive"),("Active"))

[#10]

Hi David,

That could be 

=IF(C2>0, "Active",IF(B2>0, "Dormant", "Inactive"))

and attached

[#11]

Hi Sergei, worked a treat - thanks so much.

[#12]

Hi Kelvin

Please help,i want to colour code the cell with green using if formular for a value ranging 50 to 0.That is checking with a certain cell that contains e.g numder of days 

[#13]

Hi Kelvin

Please help,i want to colour code the cell with green using if formular for a value ranging 50 to 0.That is checking with a certain cell that contains e.g numder of days 

[#14]

Hi, I got this solution to work for when I manually apply a color to a cell, but it is not working for cells that are highlighted because of the Conditional Formatting I apply on duplicate rows.

I want to easily filter duplicate rows but filtering on color is not working for more than a couple hundred rows....

Thanks!

[#15]

I have created a spreadsheet that has multiple cells that go red based on conditional formatting. I have another sheet that has hyperlink cells to the other sheets, can I get these hyperlink cells to go red if any of the cells on the hyperlinked sheet is red 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("B4").Interior.Color = Me.Range("A1").DisplayFormat.Interior.Color
End Sub

this works on the same sheet but as soon as you introduce multiple cells it turns black 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("B4").Interior.Color = Me.Range("A1:B1:C1").DisplayFormat.Interior.Color
End Sub

or cells from another sheet it fails error code 1004

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("B4").Interior.Color = Me.Range("Greenbrook!$C$3").DisplayFormat.Interior.Color
End Sub

I tried this across sheet, this is for a single cell, I'm hoping to be able to look at all cells on the said sheet and show red if any cell on the other sheet are red

[#16]

@Kevin Lehrbass 

Mabye you can help me 🙂

I have a value in cell J18 that i would like to return to cell C23 - IF - cell C19 has the color red

Is that possible? 🙂

[#17]

@Kevin Lehrbass @Laurie McDowell 

I implemented this for a similar problem and it worked well! However, if I change the color of my cell, the output value will not update unless I go into the formal and hit enter. Is there a way this will refresh automatically? I even tried the refresh function for the entire workbook, but that didn't work either.

[#18]

@Kevin Lehrbass 

Thanks for your code! I don't know why I am having trouble to make it work. The function seems to always give me value 2 whatever the colour of the cell is. Please find the spreadsheet attached (column L).

Many thanks

Paula

[#19]

@Laurie McDowell 

hi

came across this one while searching on a similar issue.

hope will be helpful ...

https://www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/comment-page-2/

good luck.

[#20]

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