copy and past conditional logic which refer do anothe cell

[zechar]

hey everyone,

I am trying to copy a conditional logic from one cell to another without having to create the same rule over and over again.
lets say i want the cells in range A1 to be red if to value in range b1 is less then 0 and gree if otherwise.

the thing is - i want it to work on all the cells between A1 to A500.

how do i do it?

thank you HELPER <3

 

[etaf]

you can just select the range you want the conditional format to apply to
so Select A1 to A500\
then apply the formula

you would need 2 formulas - one for red and one for green

I have added B1<>"" so it ignores blanks , not really needed in RED fill as a blank is seen as zero

Also you dont need Green rule , if you fill the cells A1 to A500 with Green fll manually

for Red
=AND(B1<>"",B1<0)
for green
=AND(B1<>"",B1>=0)

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>

A1 : A500

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=(B1<>"",B1<0)

Format [Number, Font, Border, Fill] Use a RED Fill
choose the format you would like to apply when the condition is true
OK >> OK

Repeat for green rule

Test Legionella2 (1).xlsx
	A	B
1
2		1
3
4		2
5
6		-1
7
8		-4
9
10		0
11
12		0
13
14		-1
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A20	Expression	=AND(B1<>"",B1>=0)	text	NO
A1:A20	Expression	=AND(B1<>"",B1<0)	text	NO

 

[zechar]

you can just select the range you want the conditional format to apply to
so Select A1 to A500\
then apply the formula

you would need 2 formulas - one for red and one for green

I have added B1<>"" so it ignores blanks , not really needed in RED fill as a blank is seen as zero

Also you dont need Green rule , if you fill the cells A1 to A500 with Green fll manually

for Red
=AND(B1<>"",B1<0)
for green
=AND(B1<>"",B1>=0)

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>

A1 : A500

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=(B1<>"",B1<0)

Format [Number, Font, Border, Fill] Use a RED Fill
choose the format you would like to apply when the condition is true
OK >> OK

Repeat for green rule

Test Legionella2 (1).xlsx
	A	B
1
2		1
3
4		2
5
6		-1
7
8		-4
9
10		0
11
12		0
13
14		-1
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A20	Expression	=AND(B1<>"",B1>=0)	text	NO
A1:A20	Expression	=AND(B1<>"",B1<0)	text	NO

thank you for the respond,

the way you showd it - the conditional formating will refer alwas to cell B1 right?

what i am looking for is to make every cell in column A to refer to the same row in column B.

For example:
A2 will refer to B2
A346 will refer to B346

Is it clearer this way?

 

[zechar]

you can just select the range you want the conditional format to apply to
so Select A1 to A500\
then apply the formula

you would need 2 formulas - one for red and one for green

I have added B1<>"" so it ignores blanks , not really needed in RED fill as a blank is seen as zero

Also you dont need Green rule , if you fill the cells A1 to A500 with Green fll manually

for Red
=AND(B1<>"",B1<0)
for green
=AND(B1<>"",B1>=0)

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>

A1 : A500

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=(B1<>"",B1<0)

Format [Number, Font, Border, Fill] Use a RED Fill
choose the format you would like to apply when the condition is true
OK >> OK

Repeat for green rule

Test Legionella2 (1).xlsx
	A	B
1
2		1
3
4		2
5
6		-1
7
8		-4
9
10		0
11
12		0
13
14		-1
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A20	Expression	=AND(B1<>"",B1>=0)	text	NO
A1:A20	Expression	=AND(B1<>"",B1<0)	text	NO

i did not undertand the part that makes the rule be generic and work on each row differently.

 

[etaf]

NO
it will do as you have shown

A2 will refer to B2
A346 will refer to B346

Conditional formatting will check the cell and then move to the next then next in the selection
So its important that the Cells selected , match with the formula

IE if you select row A3:A5 , then the formula needs to start at row 3
To get the formatting to reference a particular cells you need to use the $ in the formula

SO to fix it to always compare to B1
you use the formula
=($B$1<>"",$B$1<0)

Now all A1 to A500 will only compare to B1
BUT without the $ - it does exactly what you want

EDIT
you replied as i was typing

i did not undertand the part that makes the rule be generic and work on each row differently.

You copied the entire post of mine in your reply - so i dont know what PART you dont understand - not clear

 

[zechar]

NO
it will do as you have shown

Conditional formatting will check the cell and then move to the next then next in the selection
So its important that the Cells selected , match with the formula

IE if you select row A3:A5 , then the formula needs to start at row 3
To get the formatting to reference a particular cells you need to use the $ in the formula

SO to fix it to always compare to B1
you use the formula
=($B$1<>"",$B$1<0)

Now all A1 to A500 will only compare to B1
BUT without the $ - it does exactly what you want

EDIT
you replied as i was typing

You copied the entire post of mine in your reply - so i dont know what PART you dont understand - not clear

thank you very very much.

For answering and for teaching me what is the proper way to answer.
I will notice your reamrk about my answers.

And about the formatting - It works! Thnaks a LOT!!!

 

[etaf]

you are welcome