Conditioning format based on adjacent cell

[lokwungkwung]

Hi all:
I have a similar problem on conditioning format.
How would I make cells in column A to C be highlighted yellow, of which is 0.02 in value be higher than in compared with the adjacent cell in column D ?
It is easy to use condition format function to setup the conditions in 1 row. The problem is there are thousands rows in the sheet(of which represents an individual file by each row), and need to have the conditioning format applied on each row independently. How can it be done w/o the need to have format painter applied line by line, of which is a very time consuming.

Thanks so much
sample.xlsx

[FDibbins]

Hi and welcome to the forum

1. highlight the range you want to apply the conditional formatting to (in your sample A3:C11)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter ==OR(A3-$D3>0.02,$D3-A3>0.02) format fill color as needed

[FDibbins]

self-deleted duplicate post

[lokwungkwung]

Hi and welcome to the forum

1. highlight the range you want to apply the conditional formatting to (in your sample A3:C11)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter ==OR(A3-$D3>0.02,$D3-A3>0.02) format fill color as needed

Sincere thank for your prompt reply.

I try with your suggestion with the table resulted as and compare the table I define the CF using HL cells rule on the first row, then apply format painter line by line on the rest of the lines . Obviously the results displayed are quite different from these 2 methods. In fact the table appeared under the HL cell rules....format painter line by line is what I need. What can we do further？

Many thanks all people.

sample 1.xlsx

[newdoverman]

If you compare the number of Conditional Formatting rules in Sample 1 to Sample 2 that I have uploaded you will notice a huge difference in the number of rules.

The reason that the first set of Conditional Formatting rules differs from the second in Sample 1 is in the formula for the formatting and how it is applied. A completely different but reasonable interpretation of your requirements was given you by FDibbins. That is why his results are so different.

[lokwungkwung]

You two gentlemen are so generous.
Thanks for FDibbins's bright idea, and newdoverman's patient analysis and explanation.
My problem is perfectly tackled.

Sincerely thanks for both.

[newdoverman]

Thank you for your feedback. Helping, is what these forums are all about.