If color, then...
Solved
Debutantexcel8
Posted messages
25
Status
Membre
-
DjiDji59430 Posted messages 4276 Registration date Status Membre Last intervention -
DjiDji59430 Posted messages 4276 Registration date Status Membre Last intervention -
Hello,
I would like to know if someone can help me find an Excel formula that, based on the blue color present in cell A7 for example, returns the word "KO" in cell B7. If the color is not blue (see link to access the file: https://www.cjoint.com/c/HHqkNeoaFGm), the formula leaves the cell empty.
Is that possible?
Thank you for your help.
I would like to know if someone can help me find an Excel formula that, based on the blue color present in cell A7 for example, returns the word "KO" in cell B7. If the color is not blue (see link to access the file: https://www.cjoint.com/c/HHqkNeoaFGm), the formula leaves the cell empty.
Is that possible?
Thank you for your help.
2 réponses
Excel formulas cannot read colors.
So it is not possible directly.
And you did not understand Patrice's answer: there are many shades of blue; you need to provide the RGB code of your blue color!
The solution: You should not manually color the cell, but use a conditional formatting rule; this way, the same criterion that determines the color will serve to feed your formula.
Retirement is great! Especially in the Caribbean...
Raymond (INSA, AFPA)
So it is not possible directly.
And you did not understand Patrice's answer: there are many shades of blue; you need to provide the RGB code of your blue color!
The solution: You should not manually color the cell, but use a conditional formatting rule; this way, the same criterion that determines the color will serve to feed your formula.
Retirement is great! Especially in the Caribbean...
Raymond (INSA, AFPA)
And in VBA language?
Following some research, I found this macro that allows finding the color code:
Public Function mycolor(mycell As Range)
mycolor = mycell.Interior.Color
End Function
Then in column B of the file (see the link https://www.cjoint.com/c/HHveOnpLU7s), by typing for example in cell B2=mycolor(A2), I find the color codes of the colors as well as the blue color I want (color code = 15773696) - see the link below for file access:
Is it therefore possible for a macro to write the text "output" in each cell of column B if the macro encounters the color code 15773696 in each cell of column A?
I think you haven't explored the possibilities offered by Raymond's solution: no need for a macro!
Your method seems quite strange: using a macro to detect the color of a cell and another macro to write text in cells of the same color, with the obvious risk of having two very close shades of blue.
Raymond's method (writing the text and using a conditional formatting) determines a unique color and eliminates the aforementioned risk: it is the opposite of your method, and it is the solution recommended by the designers of Excel for managing colors.
Of course, it is possible to write a macro to automatically write the text instead of setting a color; you just need to define the conditional formatting (which can also be done by the same macro).
Best regards
Patrice
Thank you for the feedback.
I think I found it, I did some tests and it works.
In addition to the macro and the mycolor formula above, I created a new column in my table where the VLOOKUP formula writes the text "exit" based on the found color code 15773696.