Forum Discussion
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...
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
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 Good day! What formula shall I use in this table? It will base on cell color. For example, I want to add up D2 & I2 if F2 & K2 has changed color to red, black, yellow & green. The sum of D2 & I2 will be placed in N2. Thank you very much! sorry I am newbie...
Hi Kevin, thank you for your code. It works magic for me. However, can I check if you run into any issue with the file size? I used InteriorColor on an array of 5x80 cells (so 400 executions of the function). The file size changed from 110kb to 45Mb.
I’m using M365 if it matters.
Thank you
- I am use the colorindex function in several reports, but find that when the colour is changed I have either double click the cell or make a change in the table. Is there anyway way for excel to automatically calculate when cells colours are changed?
I have a similar need. My spreadsheet is populated each week with new data. Conditional formatting fills the cells in green, that have the highest value in a given column. There is a name in the first column, which needs to be placed in a given cell, when the highest value for that entry is in their row.
I'm trying to come up with a formula or function that can automatically fill in the appropriate answers for columns T and V
In V8: =MAX(L2:L17)
In T8: =INDEX(A2:A17, MATCH(V8, L2:L17, 0))
Similar for the others. If you want to be able to fill down:
In V8: =MAX(INDIRECT(U8&"2:"&U8&"17"))
In T8: =INDEX(A$2:A$17, MATCH(V8, INDIRECT(U8&"2:"&U8&"17"), 0))
Hi Kevin,
I am looking to use this to look at multiple cells at the same time. I am creating a form that highlights cells based on the information that has been entered. I want a message in the corner stating if the form is completed or not. Because the required information changes I believe the easiest way to accomplish this is to look at all the cells and countif they are green. I have attached some snips of what I am looking for it to do. I am using if statements and formatting to show and hide fields.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
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
Kevin LehrbassThanks for the great function and reminding me about custom functions. Huge help!
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
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.
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!
