Forum Discussion
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.
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 downDoes this work for you Laurie?
Cheers,
Kevin
Function InteriorColor(CellColor As Range)
Application.Volatile
InteriorColor = CellColor.Interior.ColorIndex
End FunctionYou'll have to save your file as ".xlsm" (m for macro).
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, this worked like a charm. You are the hero of the day!! Thanks ever so much!
Kevin LehrbassThanks for the great function and reminding me about custom functions. Huge help!
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
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 redhi
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.
Hopefully people are still replying to this thread!
I could really do with help here if anyone could help,
I've been asked to make it so that depending on the colour for the patient, then the date of the next review will auto populate based on the date of the last review. so if Red would be date of last review plus 7 days, if amber would be last review plus 14 days and if green would be last review plus 1 month.
I've tried to apply all that is offered in this thread but am well out of my depth!
I have a spreadsheet with employees, their name, details and their status (on shift, annual leave etc). Now i need to check in our WFM Software if there have been any new Leave requests and them change the status column for that specific employee, if they have called in sick.
Can someone help me? Paste all entries from WFM (aspect) showing annual leave or sick leave on the side of my spreadsheet. Then I applied conditional formatting so it highlights and employee ID number that is a duplicate (it is on original employee list and also in the leave list from WFM). I now want to use a macro or formula to do something like: if cell is highlighted, then change the status column of that row to the value found in the this same employees status column of the copied WFM table.
Is that possible at all?
Hello everyone,
I am new to this feed, so apologies if I am asking for something which has already been answered. I need help with the below, I have tried to break it down so I hope it helps. In a nut shell, I need to put down what the student scored correctly in G19:G23. The yellow color tabs in B3:B27 & K3:K27 reflect what the student answered incorrectly and the white tabs reflect the correct answers. It needs to correspond to the learning objective. As you will notice, it usually changes from "V" to "G" and sometimes we have "F" and "S"
Calculate "Cadet Score" by counting number of correct answered "V"s from column "Learning Objective", which correspond to the non-coloured cells in "Student Answer" column.
Calculate number of correct answered "F"s which correspond with the white coloured cells from the" Student Answer" column
Calculate number of correct answered "G"s from "Learning Objective" column, corresponding with white cells in "Student Answer" column.
Calculate number of correct answered "S"s from "Learning Objective" column which correspond with the non-coloured cells in column "Student Answer".
If there are no "V"s, "F"s, "G"s or "S"s which correspond with white cells in "Student Answer" column, mark "0" under "Cadet Score"
