Please help to understand to conditional formatting logic

[mchilapur]

Dear Excel Expertise,
I have small question regarding conditional formatting and i have been cracking my head to understand how exactly this works.

Attached file has 2 different conditional formattings using formulas with same criteria.
1. With criteria cell on right side of formatting data:
I want to know why one first column cells get formatted.? I am assuming, when i write relative references, conditional
formatting will check for criteria in all cells ($A$2:$D$7) and must color it. But my assumption is wrong in this case as it stops at first column itself.
Please help me to understand the logic behind.

2. With criteria cell on left side of formatting data:
This works as per my assumption above..:D . Will conditional formatting check for criteria in all cells ($A$10:$D$15) and colour it if its true.?
why it will not do the same when criteria is on right side.? Obviously there is a difference between both scenarios and i am eager to understand it.

My question may be silly but will be of great help if anyone can help me to understand this difference.

[etaf]

you have said D2 = YES
so that will apply for A2:D7 cells

BUT it starts at the start of the range

so in A2 it is checking if D2 =Yes
in B2 its checking E2
C2 checking F2
etc

add a dollar

why are you starting D2

Whats the rules meant to do

IF A2 = Yes highlight
OR is it to do with column D

explain the RULES

A2 will be coloured - BECAUSE
B2 will be coloured - BECAUSE
etc

[CK76]

Right to Left:

Your formula is applied to $A$2:$D$7 range.

So, relative reference when formula is evaluated for A2 is D2. All columns to right of that will then be offset by one column to right.
Those are all empty, so your formatting isn't applied.

Left to Right:
Your formula is applied to $A$10:$D$15

Relative reference at A10 is set to A1. So all the cells are checking corresponding cell in $A$1:$D$6 range.
And isn't really checking what you are expecting.

What is it exactly that you are looking to do?
Detail in word which cell should be checked for which cell (give few examples for both cases).

[sfriar616]

Your formula was wrong.
I presume Cell D2 will always contain "Yes", so if you use the formula

Please Login or Register  to view this content.

and then input your formatting it should work as per attached

[mchilapur]

Hi Etaf,
I think i got your point and understood the logic behind... Relative reference is the concept behind.!
Many thanks for your explanation.

[mchilapur]

Hi CK76,
Same is the case with your explanation... THough i got confused with below explanation, but i got the point..

Left to Right:
Your formula is applied to $A$10:$D$15

Relative reference at A10 is set to A1. So all the cells are checking corresponding cell in $A$1:$D$6 range.
And isn't really checking what you are expecting.

Many thanks for your explanation.