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]

When I first looked at the image of you data sample, it looked like the cells in columns E and F were merged cells. Turns out they weren't. That meant that the CF formulas had to be different in the even and odd numbered rows.
As such, the file I shared with you, with all the correct CF formulas inserted, had each odd numbered row referring to the same row in columns E and F - whereas the formulas in the even numbered rows had to refer to columns E and F one row ABOVE.
This means that you cannot simply extend the scope of the CF by merely increasing the Apply to range, because they subsequent formulas would only be correct 50% of the time. You need to get 2 rows correct (an odd and even row) and then copy the format from BOTH rows to subsequent rows in pairs.
This is what I did in the last file I shared with you - tedious process but only has to be done once.
I'm willing to help you again. If you share the latest incarnation of your file, I will put the CF in for you. However, given that you have previously "finalised" this thread twice already, it will be last time I will respond to requests for further "tweaks", and any further changes you make I can only suggest that you start a new thread. It's after midnight in my part of the world, so it'll be a job for tomorrow.

 

[amaresh achar]

When I first looked at the image of you data sample, it looked like the cells in columns E and F were merged cells. Turns out they weren't. That meant that the CF formulas had to be different in the even and odd numbered rows.
As such, the file I shared with you, with all the correct CF formulas inserted, had each odd numbered row referring to the same row in columns E and F - whereas the formulas in the even numbered rows had to refer to columns E and F one row ABOVE.
This means that you cannot simply extend the scope of the CF by merely increasing the Apply to range, because they subsequent formulas would only be correct 50% of the time. You need to get 2 rows correct (an odd and even row) and then copy the format from BOTH rows to subsequent rows in pairs.
This is what I did in the last file I shared with you - tedious process but only has to be done once.
I'm willing to help you again. If you share the latest incarnation of your file, I will put the CF in for you. However, given that you have previously "finalised" this thread twice already, it will be last time I will respond to requests for further "tweaks", and any further changes you make I can only suggest that you start a new thread. It's after midnight in my part of the world, so it'll be a job for tomorrow.

Oh.. I am extremely sorry Sir... I didnt know its such a tedious task... Now I know what needs to be done... I'll try doing as you explained.... And thank you so much for the help Sir...

 

[kevin9999]

Oh.. I am extremely sorry Sir... I didnt know its such a tedious task... Now I know what needs to be done... I'll try doing as you explained.... And thank you so much for the help Sir...

If you run into problems, let me know and share the file. And note that you can copy as many rows (to grab the formats) as you like - as long as it's an even number, 2 or 200...