SOLVED

Trying to use 2 variables to color a cell

Copper Contributor

Hello, I have been trying to change a cell's fill color based on 2 variables, and haven't been able to crack it. If the values in D are greater than 30, AND, the value in C is "TRUE", then the cell in D should have a red fill. In this example, D11 should have a red fill.  Thank You

 

Sample.PNG

3 Replies
best response confirmed by dantheman1425 (Copper Contributor)
Solution

@dantheman1425 

That could be

image.png

 

I don't know how that works, but it does! Thank You!

@dantheman1425 , you are welcome

We apply conditional formatting for the range $D$2:$D$11. CF iterates each cell in that range applying the formula defined for the first cell in the range.

$D2>30  checks if the value greater than 30 and returns TRUE or FALSE

Staying on $D2 we also take value from $C2, reference on column C is fixed with $.

In Excel TRUE and FALSE considered as 1 and 0 accordingly. Thus formula returns 1 or 0 depends on calculation result.

If 1, which is considered as TRUE, Excel applies formatting, otherwise does nothing and goes on next cell in the range to check the rule.

In formula we have relative row references, thus in D3 it will be calculated

=($D3>30)*$C3

and so on for each cell.

1 best response

Accepted Solutions
best response confirmed by dantheman1425 (Copper Contributor)