• 379K Members
• 3,470 Online
• 393K Conversations
SOLVED

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

16 Replies
Solution

# Re: Formula or function for IF statement based on cell color

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

# Re: Formula or function for IF statement based on cell color

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

# Re: Formula or function for IF statement based on cell color

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

# Re: Formula or function for IF statement based on cell color

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

# Re: Formula or function for IF statement based on cell color

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.

# Re: Formula or function for IF statement based on cell color

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

# Re: Formula or function for IF statement based on cell color

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

# Re: Formula or function for IF statement based on cell color

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

# Re: Formula or function for IF statement based on cell color

Hi David,

That could be

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

and attached

# Re: Formula or function for IF statement based on cell color

Hi Sergei, worked a treat - thanks so much.

# Re: Formula or function for IF statement based on cell color

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

# Re: Formula or function for IF statement based on cell color

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

# Re: Formula or function for IF statement based on cell color

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!

# Re: Formula or function for IF statement based on cell color

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

# Re: Formula or function for IF statement based on cell color

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?

# Re: Formula or function for IF statement based on cell color

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.