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.

 

[amaresh achar]

"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."

Sorry Sir. I was facing some issues with google drive page, so could not upload/ share the link... so.. here is the link:

Blank Template Mr. Excel.xlsm

Report PART INSPECTION STANDARD CUM REPORT Part Number,PIS Rev No,Inspection Date (Supplier) Part Description,Revised By,Inspected by (Supplier) Supplier Code,Revised on,Inspection Date (IQC) Supplier Name,GRIN No,GRIN Qty,Inspected by (IQC) SN,Parameter,Type,Specification,Min (Ref),Max (Ref),In...

docs.google.com

"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?" -- Yes Sir.. you are exactly right... and similaraly for subsequent rows..

"Thirdly, why do you need to use VBA?"
I need to add some VBA script in the file for some other tasks like color, font, cell protection, cell merge etc. So I thought it would be better to have all the tasks to be scripted in a single program. But if it can be done with formula even then its great.

"Finally, are those merged cells in columns E & F? If so, the task becomes a lot more difficult?"
Yes... I see why so many excel experts don't recommend merging of cells... But Sir... nothing to worry about... it's just for aesthetics... and good report visibility on the display screen.. And I checked the validity of formulas.. and it works even with merged cell.. (If correct cell refence is given in the formula i.e Exact reference as given in the name box when I selected the merged cell)

 

[kevin9999]

Thank you Amaresh for your detailed response. This will require only minor changes to the existing CF: use absolute references to columns E & F, and adjust the Apply to range. Change the Apply to range from $J$9 to $J$9:$S$508. Details below, and link to the file with the changes applied here Blank Template Mr. Excel.xlsm

Blank Template Mr. Excel.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
8	Min (Ref)	Max (Ref)	Inspection Gauge / Tool / Method	Assy Critical	Inspector	1	2	3	4	5	6	7	8	9	10
9	287.2	289.2	Tool-A-1	N	Supplier	289.2
10					IQC						180
11	Min-A-2	Max-A-2	Tool-A-2	Y	Supplier
12					IQC
13	Min-A-3	Max-A-3	Tool-A-3	N	Supplier
14					IQC
15	Min-A-4	Max-A-4	Tool-A-4	Y	Supplier
16					IQC
17	Min-A-5	Max-A-5	Tool-A-5	N	Supplier
18					IQC
19	Min-A-6	Max-A-6	Tool-A-6	Y	Supplier
20					IQC
21	Min-A-7	Max-A-7	Tool-A-7	N	Supplier
22					IQC
23	Min-A-8	Max-A-8	Tool-A-8	Y
24
25	Min-A-9	Max-A-9	Tool-A-9	N
26
Report

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J9:S508	Expression	=AND(J9<>"",OR(J9<$E9,J9>$F9))	text	NO
J9:S508	Expression	=AND(J9<>"",J9>=$E9,J9<=$F9)	text	NO
H8:I8	Cell Value	="Y"	text	NO
H8:I8	Cell Value	="N"	text	NO

 

[amaresh achar]

Sir, Thank you so much for the response... but it says "Sorry, the file you have requested does not exist."

I guess, the download link is expired.. Can you please re-upload the file...??

 

[kevin9999]

Sorry, there you go: Blank Template Mr. Excel.xlsm

I added a few values in to demonstrate the CF - you can easily remove them.

 

[amaresh achar]

Thank you so so much Sir..!!! that works great...!!! (This is better, quick and dynamic compared to my idea of including the task in VBA)

 

[amaresh achar]

I tried copying the same formula for larger range Sir... but I seem to have done something wrong... every alternate row (IQC) is not formatting as per rule... Can you please help me out here...!?

Sample Report 3G_VU_version_Mr.Excel.xlsm

drive.google.com

 

[kevin9999]

I tried copying the same formula for larger range Sir... but I seem to have done something wrong... every alternate row (IQC) is not formatting as per rule... Can you please help me out here...!?

Sample Report 3G_VU_version_Mr.Excel.xlsm

drive.google.com

View attachment 95042

The file with the link in post #14 had the correct formulas in the entire range down to row 508. Did you try putting some values in the linked file to test the CF, rather than trying to copy the format yourself?

 

[amaresh achar]

The file with the link in post #14 had the correct formulas in the entire range down to row 508. Did you try putting some values in the linked file to test the CF, rather than trying to copy the format yourself?

Sir...In the previous file... I wanted the Conditional formatting from J0 to S508...
Later I realised that I need to have more rows... so increased the selection range upto S1034... And now the range is starting from J21...
So I wanted to tweak the formula accordingly.. but formula could not be edited...
So I typed same formula just replaced row9 with row25...

 

[yaswanth0291]

Its great that you are doing this one Amaresh. good work keep it up

 

[amaresh achar]

Sir...In the previous file... I wanted the Conditional formatting from J0 to S508...
Later I realised that I need to have more rows... so increased the selection range upto S1034... And now the range is starting from J21...
So I wanted to tweak the formula accordingly.. but formula could not be edited...
So I typed same formula just replaced row9 with row25...

So I typed same formula and just replaced row9 with row21