Can 'Conditionally Formatted' cells be use as a logic in IF statements etc?

[nosajnid89]

To make it simpler, I understand how 'Conditional Formatting' works and No question about it. What I want to achieve is to use Conditionally Formatted cells (e.g with Fill Color) in an IF Statement.

For example:
IF cell (A1) has fill color 'Green' and text value "Green Highlighted" done by 'Conditional Formatting', then cell (B1) should also have text value "Green", otherwise just text "No Color".

I want to be able to use any 'Conditional Formatting' rules or its properties on any excel formulas.
I have tried using GET.CELL functions with type number 38 to get the fill color, but its working only in manually formatted cells NOT in Conditionally Formatted cells. See the screenshot below.

The first 4 colored rows are done by ''Conditional Formatting'' but returned 0, and the last 4 colored cells are done manually it returned their actual number. All this was done by using =GET.CELL(38,A2:A9) function.
With the help of the NUMBER of those colors, I will be able to achieve my requirements.

Kindly let met know if there is a way to get the properties used in 'Conditional Formatting', specially the number of the color applied. But I will greatly appreciate if there other ways or option that you can suggest.

 

[nosajnid89]

Now we're getting somewhere, thank you. Yes, we can make a formula for column B that looks for the text in column A.

Something like this perhaps:

=IF(SEARCH("Facility 1",A2),xxx,"")

You mentioned you already have a SUMIF formula in column B, so you would put that formula into the above formula where I put the "xxx". Let us know if that works.

Hi @dreid1011 this is great. I'm tested it and it works. Thanks so much.

But 1 last thing, what if I will be searching for 3 substrings e.g. "Facility 1", "Facility 2", "Facility 3". How should I use OR functions here?

 

[dreid1011]

Like this:

=IF(OR(SEARCH("Facility 1",A2),SEARCH("Facility 2",A2),SEARCH("Facility 3",A2)),xxx,"")

 

[nosajnid89]

Like this:

=IF(OR(SEARCH("Facility 1",A2),SEARCH("Facility 2",A2),SEARCH("Facility 3",A2)),xxx,"")

Hi @dreid1011

The formula always returning 0 value, I don't know why. But this helps me save a lot of time analyzing the logic.

I used this formula instead:

=IF(ISNUMBER(SEARCH("Facility 1", A2)),"True Value", IF(ISNUMBER(SEARCH("Facility 2", A2)),"True Value", IF(ISNUMBER(SEARCH("Facility 3", A2)),"True Value", "False Value")))

I completed my task already and I really appreciate your help and sharing your knowledge.

Mandaang guwu,

 

[dreid1011]

Glad you got it figured out.

 

[RoryA]

FYI, you can shorten that using something like:

=IF(COUNT(SEARCH({"Facility 1","Facility 2","Facility 3"},A2)),"True Value","False value")