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.

 

[dreid1011]

Unfortunately, you cannot check for formatting in a formula. However, you can use the same logic that drives the CF rule in your formula.

IE:
CF rule to color all cells in column A green if the value in column B is greater than 5.
Your CF rule would look like this:
=B1>5

Your IF formula would look like this:
=IF(B1>5, true , false )

If you can tell us what why the text in column A should be green, we can help you come up with a formula for column B.

 

[nosajnid89]

Unfortunately, you cannot check for formatting in a formula. However, you can use the same logic that drives the CF rule in your formula.

IE:
CF rule to color all cells in column A green if the value in column B is greater than 5.
Your CF rule would look like this:
=B1>5

Your IF formula would look like this:
=IF(B1>5, true , false )

If you can tell us what why the text in column A should be green, we can help you come up with a formula for column B.

Thank you for your answer.

As use case, I would like any cells in column A highlighted with Green should tell column B to pull data and run calculations then hold the end value. If NOT Green, then cell in column B will just stay "" or 0.
The output should be something like this:

I will appreciate any workarounds you can suggest. Thanks again.

 

[dreid1011]

Thank you for your answer.

As use case, I would like any cells in column A highlighted with Green should tell column B to pull data and run calculations then hold the end value. If NOT Green, then cell in column B will just stay "" or 0.
The output should be something like this:
View attachment 101053
I will appreciate any workarounds you can suggest. Thanks again.

Okay, but what is the reason the cell in column A should be green? We need the logic behind it to know how to make the formula. And where is the data being pulled from? Please provide exact steps on what you want to happen and where all the data is located. More samples of your data are super helpful too.

 

[nosajnid89]

Okay, but what is the reason the cell in column A should be green? We need the logic behind it to know how to make the formula. And where is the data being pulled from? Please provide exact steps on what you want to happen and where all the data is located. More samples of your data are super helpful too.

Green is just a color I like, that represents a Go signal (meaning my process is a go). I can use another color actually.

I'm using SUMIF to pull data from another sheet within a certain 'Range', no problem on this part.

I just need a formula that identifies any cells in Column A color codes and I will combine my existing formula in Column B.
Please note Column A is formatted using CF but I can work with other ways as long as Column A is still color coded.

 

[awoohaw]

Green is just a color I like, that represents a Go signal (meaning my process is a go). I can use another color actually.

I'm using SUMIF to pull data from another sheet within a certain 'Range', no problem on this part.

I just need a formula that identifies any cells in Column A color codes and I will combine my existing formula in Column B.
Please note Column A is formatted using CF but I can work with other ways as long as Column A is still color coded.

But @nosajnid89 , if you don't have a consistent rule for coloring column A you cannot put that rule into column B.
Your formula in Column be needs to be something like this:
=IF("Cell In Column A Meets Rule", <<PULL DATA and run Calculations, returning final Value>>, << "" or 0>>)

and note: The way you describe coloring the cells in column A is not conditional formatting.

 

[dreid1011]

Green is just a color I like, that represents a Go signal (meaning my process is a go). I can use another color actually.

I'm using SUMIF to pull data from another sheet within a certain 'Range', no problem on this part.

I just need a formula that identifies any cells in Column A color codes and I will combine my existing formula in Column B.
Please note Column A is formatted using CF but I can work with other ways as long as Column A is still color coded.

But @nosajnid89 , if you don't have a consistent rule for coloring column A you cannot put that rule into column B.
Your formula in Column be needs to be something like this:
=IF("Cell In Column A Meets Rule", <<PULL DATA and run Calculations, returning final Value>>, << "" or 0>>)

and note: The way you describe coloring the cells in column A is not conditional formatting.

Okay, let's try this again.. and building from what awoohaw said..

How do you decide to color the cell in column A? (the color you choose does not matter)

Is there a specific value in column A that means it should be colored? Is there a specific date that means it should be colored? HOW are you deciding to color the cells? We cannot make a formula that will detect your coloring by itself. There has to be a reason you color a specific cell.

What tells you that your process is a go?

 

[nosajnid89]

But @nosajnid89 , if you don't have a consistent rule for coloring column A you cannot put that rule into column B.
Your formula in Column be needs to be something like this:
=IF("Cell In Column A Meets Rule", <<PULL DATA and run Calculations, returning final Value>>, << "" or 0>>)

and note: The way you describe coloring the cells in column A is not conditional formatting.

Thank you for your answer.

The formula you provided will be the one I will use in column B. The only thing I'm missing is, how can I identify/check the cells in Column A that this formula be applied to.

Aside from color coding, I can use a 'Text' value that the cell in column A contains. I just don't know the correct syntax.

I will appreciate any suggestions to meet the requirements. Thanks again.

 

[nosajnid89]

Okay, let's try this again.. and building from what awoohaw said..

How do you decide to color the cell in column A? (the color you choose does not matter)

Is there a specific value in column A that means it should be colored? Is there a specific date that means it should be colored? HOW are you deciding to color the cells? We cannot make a formula that will detect your coloring by itself. There has to be a reason you color a specific cell.

What tells you that your process is a go?

Sorry for missing some details. So this how I do it:

1. Using CF, I create a rule w/ type "Format only cells that contain " >> Specific text containing 'Facility 1'
I applied a color Green to highlight this format.

2. In Column A, I have picklist that contains 'Company Facility 1 Inc.'. Whenever this is selected, CF rule applies in the same cell (A2) and highlighted in green.

3. In Column B, cell (B2) the formula that awoohaw said should be applied.

I can use the Color or Text it contains or other properties, whatever that works best as identifiers.

 

[dreid1011]

Sorry for missing some details. So this how I do it:

1. Using CF, I create a rule w/ type "Format only cells that contain " >> Specific text containing 'Facility 1'
I applied a color Green to highlight this format.

2. In Column A, I have picklist that contains 'Company Facility 1 Inc.'. Whenever this is selected, CF rule applies in the same cell (A2) and highlighted in green.

3. In Column B, cell (B2) the formula that awoohaw said should be applied.

I can use the Color or Text it contains or other properties, whatever that works best as identifiers.

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.