[Excel] Test on the color of a cell
Solved
graillou
-
Raymond PENTIER Posted messages 58551 Registration date Status Contributeur Last intervention -
Raymond PENTIER Posted messages 58551 Registration date Status Contributeur Last intervention -
Hello, I would like to create a function in Excel to test the color of a cell based on conditional formatting.
For example, if my cell A1 has a green background, then my cell B1 will have the letter "V" as content, and if my cell A1 has a red background, then my cell B1 will have the letter "F" as content.
Can you help me? (If possible without macros or VBA)
Thank you in advance for any help
For example, if my cell A1 has a green background, then my cell B1 will have the letter "V" as content, and if my cell A1 has a red background, then my cell B1 will have the letter "F" as content.
Can you help me? (If possible without macros or VBA)
Thank you in advance for any help
Configuration: Windows 2000 Internet Explorer 6.0
3 réponses
Hello, graillou.
If I understood your problem correctly, the answer has already been provided by coco49rico to Nathalie on August 14th.
Adapting it to your specific example, it would look like this in cell A1:
* click on: Format/Conditional Formatting menu
* in the first box of the dialog window, replace the highlighted option "Cell value is" with the other option "Formula is" (arrow to the right).
* in the right box, enter the formula "=EXACT(B1, "V")
* click on the [Format...] box and choose the red background color in the [Fill] tab
* click [OK]; you'll see a preview of the format
* click on [Add >>]
* in the 2nd part of the window (Condition 2), do the same by replacing "V" with "F" and choosing the red background. Confirm by [OK].
That's it, and it works; you even have the right to a 3rd (and last) condition if necessary!
Cheers...
If I understood your problem correctly, the answer has already been provided by coco49rico to Nathalie on August 14th.
Adapting it to your specific example, it would look like this in cell A1:
* click on: Format/Conditional Formatting menu
* in the first box of the dialog window, replace the highlighted option "Cell value is" with the other option "Formula is" (arrow to the right).
* in the right box, enter the formula "=EXACT(B1, "V")
* click on the [Format...] box and choose the red background color in the [Fill] tab
* click [OK]; you'll see a preview of the format
* click on [Add >>]
* in the 2nd part of the window (Condition 2), do the same by replacing "V" with "F" and choosing the red background. Confirm by [OK].
That's it, and it works; you even have the right to a 3rd (and last) condition if necessary!
Cheers...
Thank you for your solution, it works very well but it's exactly the opposite of what I want to do.
This procedure tests the content of a cell and then colors the background of another. But I want to test the background color of a cell regardless of what is in it, and fill another cell based on the color code returned by the test.
I'm not sure if I'm very clear... even I....
Once again, thank you. And if you have an idea, please don't hesitate ;-)
I’m proposing a small macro that does this:
Sub distri_couleur()
Dim i
For i = 1 To 12
Worksheets(1).Range("C" & i).Value = Worksheets(1).Range("B" & i).Interior.Color
Next i
End Sub
'Replace 10 with the number of rows you want to process.
'Replace "I" with the column you want to use for the result (color code).
'Replace "B" with the column that contains the colors to filter.
You get a coded number that corresponds to the color, and then you handle this code as needed.
--
*** @+ / Furtif ***
*** Eat bananas! ***