Forum Discussion
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 downDoes this work for you Laurie?
Cheers,
Kevin
Function InteriorColor(CellColor As Range)
Application.Volatile
InteriorColor = CellColor.Interior.ColorIndex
End Function
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"
