Switch cell with color based on 3 different conditions

[amaresh achar]

Hi..

I need help with conditional formatting using formula:

I have the following values in my sheet:

E18: 287.2
F18: 289.2

If J18 is blank, then NO FILL ;
If J18 cell value is between cell values of E18 and F18, then fill J18 cell with LIGHT GREEN background color and DARK GREEN Text color
else fill J18 cell with LIGHT RED background color and DARK RED Text color

Thanks in advance.

 

[kevin9999]

Does this satisfy your conditions?

Book1
	E	F	G	H	I	J
18	287.2	289.2				288
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J18	Expression	=AND(J18<>"",J18>E18,J18<F18)	text	YES
J18	Expression	=AND(J18<>"",OR(J18<E18,J18>F18))	text	YES

Book1
	E	F	G	H	I	J
18	287.2	289.2				280
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J18	Expression	=AND(J18<>"",J18>E18,J18<F18)	text	YES
J18	Expression	=AND(J18<>"",OR(J18<E18,J18>F18))	text	YES

Book1
	E	F	G	H	I	J
18	287.2	289.2
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J18	Expression	=AND(J18<>"",J18>E18,J18<F18)	text	YES
J18	Expression	=AND(J18<>"",OR(J18<E18,J18>F18))	text	YES

 

[amaresh achar]

Does this satisfy your conditions?

Book1
	E	F	G	H	I	J
18	287.2	289.2				288
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J18	Expression	=AND(J18<>"",J18>E18,J18<F18)	text	YES
J18	Expression	=AND(J18<>"",OR(J18<E18,J18>F18))	text	YES

Book1
	E	F	G	H	I	J
18	287.2	289.2				280
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J18	Expression	=AND(J18<>"",J18>E18,J18<F18)	text	YES
J18	Expression	=AND(J18<>"",OR(J18<E18,J18>F18))	text	YES

Book1
	E	F	G	H	I	J
18	287.2	289.2
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J18	Expression	=AND(J18<>"",J18>E18,J18<F18)	text	YES
J18	Expression	=AND(J18<>"",OR(J18<E18,J18>F18))	text	YES

Yes , this satisfies my condition.. is there a single formula to update in conditional formatiing..??

 

[kevin9999]

is there a single formula to update in conditional formatiing..??

A single formula - no, because you don't have a single format you're looking for.

 

[amaresh achar]

Sorry... I didn't understand how to use the solution... which formula should I copy and how do I use it...?

 

[kevin9999]

In your existing file, select cell J18.
From the Home menu, select Conditional Formatting/New Rule
Under Select a Rule Type - select Use a formula to determine which cells to format
Under Format values where this formula is true - copy the second formula and paste it into the box
Press the Format button, and set the format (fill & font) that you want for that condition, then OK to finish
Repeat with the second formula/format
Your Conditional formatting rule manager should look like this:

 

[amaresh achar]

In your existing file, select cell J18.
From the Home menu, select Conditional Formatting/New Rule
Under Select a Rule Type - select Use a formula to determine which cells to format
Under Format values where this formula is true - copy the second formula and paste it into the box
Press the Format button, and set the format (fill & font) that you want for that condition, then OK to finish
Repeat with the second formula/format
Your Conditional formatting rule manager should look like this:

Wow... works great... Thank you so much Sir...!!

 

[kevin9999]

You're more than welcome Amaresh, and thanks for the feedback

 

[amaresh achar]

You're more than welcome Amaresh, and thanks for the feedback

Sir, can you please assist me to reproduce the formula using VBA for the range of cells as shown in the image...? and do the same for rest of the rows until the last row...?

 

[kevin9999]

Firstly, I can't copy an image, and have no intention of recreating what your image shows for the purpose of providing a solution. Please provide a copy of your sheet using the XL2BB add-in, or better still share your file via Google Drive, Dropbox or similar file sharing platform.
Secondly, where you say the "range of cells" do you mean all the cells in columns J to S, from row 9 to the last row - all of which are referencing columns E & F?
Thirdly, why do you need to use VBA?
Finally, are those merged cells in columns E & F? If so, the task becomes a lot more difficult?