Change the formatting of a cell depending on the value of that cell and another cell

EDIT:

Okay. Thanks to BruceWayne, I have made progress.

However, the formula I ended up with: `=AND(\$C3="Y",OR(\$G3<=0.5,\$H3<=0.5,\$I3<=0.5,\$J3<=0.5,\$K3<=0.5,\$L3<=0.5,\$M3<=0.5,\$N3<=0.5,\$O3<=0.5,\$P3<=0.5))`

turns all the conditionally formatted cells of the row green simply because there is a Y in the cell of column `C`.

The formula should ideally check if a cell in column `C` has a Y in it, and a cell on the same row within columns `H:P` has a value <=0.5. If both values are TRUE, then only the cell with a value <=0.5 should be formatted GREEN.

In my included picture, cell `H7` is colored GREEN even though it is 0.99 (>0.5). This appears to be because the formula is only checking if a Y is in column `C` row `7`.

I don't understand Excel conditional formatting jargon so please explain it in layman's terms.

If a cell (eg: `C1`) in column `C` contains the value: Y, and ANY cell of the same row in columns `H:K` is =<0.5 (e.g. `K1`), then make those cells (e.g. `K1`) turn GREEN. The (G) in the diagram denotes a cell that should be green.

(Diagram for clarity)

``````| Column C | Column H | (Column MID) | Column K |
|          |          |              |          |
|    Y     |   0.6    |     0.9      |  0.3(G)  |
|          |          |              |          |
| (empty)  |   0.5    |     0.4      |  0.3     |
|          |          |              |          |
|    Y     |   0.5(G) |     0.2(G)   |  0.9     |
``````

I tried `=AND(\$C\$3="Y",\$G\$3:\$P\$75="<0.5")`

I tried `=IF(AND(\$C\$3:\$C\$75="Y",\$G\$3:\$P\$75="<0.5"),"")`

I plugged both formulas into the conditional format manager and set the output format to GREEN, but I'm missing something because no cells turned green.

• Thank you robinCTS for the clean edits. Others had trouble with my question as it was, but it reads much more clearer now. Jun 14 '18 at 16:28

You actually only need three conditional formatting rules - your third one is redundant.

To work correctly as required, the formula for your last rule needs to be changed to:

``````=AND(\$C3="Y",G3<=0.5,G3<>"")
``````

To be able to write/modify conditional formatting formulas, you need to know a couple of things.

Firstly you need to understand the difference between relative and absolute addressing.

Basically, absolute addressing is when there is a `\$` in front of the letters and/or the numbers of an address, e.g. the column `C` in `\$C3` is an absolute column reference. As you move from cell to cell to the right, the example address, `\$C3`, remains the same as the column is absolute. (As you move down, though, the address will change, as the row is relative.)

Relative addressing is when there is no `\$`, e.g. the row `3` in `\$C3` and the column `G` in `G3`. As you move to the right, the column part of that address changes. For `G3`, in the next cell to the right it changes to `H3`. For the next cell along it is `I3`, and so on.

The second thing you need to know is that when entering the formula for a conditional formatting rule, it is treated as if it were entered in the top-left cell of the "Applies to" range, and when Excel applies it to the cells in that range to work out the cells' formats, the relative references change as described above.

``````=AND(\$C3="Y",OR(\$G3<=0.5,\$H3<=0.5,\$I3<=0.5,\$J3<=0.5,\$K3<=0.5,\$L3<=0.5,\$M3<=0.5,\$N3<=0.5,\$O3<=0.5,\$P3<=0.5))
``````

is actually doing (for the first row of the "Applies to" range) is, "for every cell in the row, if `\$C3="Y"` and if any of the cells from `\$H3` to `\$P3` is <= 0.5, then set the cell's colour to green". The same thing happens, appropriately, for every other row in the "Applies to" range.

What you need to be doing instead is "for every cell in the row, if `\$C3="Y"` and if the current cell is <= 0.5, then set the cell's colour to green".

Since, as I have explained above, when the formula is entered in the Conditional Formatting Rules Manager it is treated as if entered in the top-left cell of `\$G\$3:\$P\$75`(cell `\$G\$3`), "the current cell" is the relative reference `G3`.

Notes:

For your formula to actually work correctly, a check to see if the cell is not blank is also required. A blank cell is treated as a `0` when comparing with a number, and since 0 <= 0.5, all the blank cells would be coloured green.

• Wow! Thank you so much for that clarification and respectful answer, robinCTS! I appreciate you taking the time to spell it out for me. I'm also grateful you recognized a major flaw in that 0<=0.5, because I had completely missed this point. Jun 14 '18 at 16:24

You can do:

``````=AND(\$C2="Y",OR(\$H2<=.5,\$I2<=.5,\$J2<=.5,\$K2<=.5))
``````

• Thank you Batm- BruceWayne, this worked, except under the "Applies to:" box, I used =\$G\$3:\$P\$75. These are the cells I want to turn green, and they did. However, the entire row turned green starting from G3 to P3 if C3 was TRUE. I only want the cells that contain the value <=0.5 to be green as there are other values on those rows that might be >0.5 and therefore should not be green. Jun 12 '18 at 21:59
• I've posted a picture clarifying my problem with BruceWayne's formula. It worked, but not as expected. I have green rows where individual cells should be green. I adjusted the formula according to my specific needs as per other suggestions, but even with the original formula idea, green rows were produced. Also, in the picture, H7 is Green, but it shouldn't be because it is >0.5 Jun 12 '18 at 22:23