SOLVED

Formula or function for IF statement based on cell color

Copper Contributor

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.

 

excel.JPG

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

 

 

 

 

 

32 Replies
best response confirmed by Laurie McDowell (Copper Contributor)
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 

if colors different then add else calc abs difference.JPGStep 1.JPG

 

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

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

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

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.

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

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

 

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

 

Capture1.JPG

Hi David,

 

That could be 

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

and attached

Hi Sergei, worked a treat - thanks so much.

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 

 

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 

 

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!

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

@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? :)

 

 

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

@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

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

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

1 best response

Accepted Solutions
best response confirmed by Laurie McDowell (Copper Contributor)
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 

if colors different then add else calc abs difference.JPGStep 1.JPG

 

View solution in original post