Conditional formatting formula - multi criteria please

[Orangeanorak]

conditional formatting?

I posted something similar to this some time ago but never got an answer and it 'fell off' the board. Here's hoping for a solution this time please.

1. Formula please to highlight the cell in column H that is equal to or nearest to J1 IF the cell in the same row in column J equals 7


2. Formula to highlight the cell in column B if the cell on the same row in column H is highlighted as a result of the above.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	*	*	*	*	*	*	*	*	*	17
2	*	*	*	*	*	*	*	*	*	*
3	*	*	*	*	*	*	*	*	*	*
4	1	Red	*	*	*	*	*	7	*	7
5	2	Black	*	*	*	*	*	8	*	7
6	3	Blue	*	*	*	*	*	17	*	3
7	4	Green	*	*	*	*	*	8	*	7
8	5	White	*	*	*	*	*	49	*	6
9	6	Brown	*	*	*	*	*	5	*	2
10	7	Grey	*	*	*	*	*	31	*	5
11	8	Scarlet	*	*	*	*	*	11	*	6
12	9	Yellow	*	*	*	*	*	13	*	5
13	10	Orange	*	*	*	*	*	22	*	1
Sheet

Thank you - your help is appreciated

 

[Aladin Akyurek]

Aladin, that does not address the highlighted condition below

An oversight. Thanks for pointing that out. Adding thet condition, we get

=AND($J4=7,$H4=INDEX($H$4:$H$13,MATCH(MIN(ABS(IF($J$4:$J$13=7,$H$4:$H$13)-$J$1)),ABS(IF($J$4:$J$13=7,$H$4:$H$13-$J$1)),0)))

 

[Orangeanorak]

Try this.
1. Select B4:B10 and H4:H10, ensuring either B4 or H4 is the active cell.
2. Apply the CF shown.

Excel Workbook
	B	C	D	E	F	G	H	I	J
1	*	*	*	*	*	*	*	*	17
2	*	*	*	*	*	*	*	*	*
3	*	*	*	*	*	*	*	*	*
4	Red	*	*	*	*	*	7	*	7
5	Black	*	*	*	*	*	8	*	7
6	Blue	*	*	*	*	*	17	*	3
7	Green	*	*	*	*	*	8	*	7
8	White	*	*	*	*	*	49	*	6
9	Brown	*	*	*	*	*	5	*	2
10	Grey	*	*	*	*	*	31	*	5
11	Scarlet	*	*	*	*	*	11	*	6
12	Yellow	*	*	*	*	*	13	*	5
13	Orange	*	*	*	*	*	22	*	1
14	*	*	*	*	*	*	*	*	*
CF Nearest with Condition

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4	1. / Formula is =AND($J4=7,ABS($J$1-$H4)=MIN(IF($J$4:$J$13=7,ABS($H$4:$H$13-$J$1))))	Abc

Thank you Peter SS this works perfectly

Aladin - Thank you for your formula - it covered everything except in situations where a cell in H4:H13 does not match J1 exactly the HIGHER AND LOWER nearest numbers to the J1 number did not both show the formatting.

 

[Aladin Akyurek]

Thank you Peter SS this works perfectly

Aladin - Thank you for your formula - it covered everything except in situations where a cell in H4:H13 does not match J1 exactly the HIGHER AND LOWER nearest numbers to the J1 number did not both show the formatting.

You probably missed the edit...

http://www.mrexcel.com/forum/excel-...ormula-multi-criteria-please.html#post3790918

 

[Peter_SSs]

You probably missed the edit...

http://www.mrexcel.com/forum/excel-...ormula-multi-criteria-please.html#post3790918

I believe what the OP is saying is that in the sample, if H5 was changed to 26, both H5 and H7 should be highlighted because they are both 9 away from J1.

 

[Orangeanorak]

I believe what the OP is saying is that in the sample, if H5 was changed to 26, both H5 and H7 should be highlighted because they are both 9 away from J1.

Thats it. Thank you for explaining this more clearly

 

[Orangeanorak]

And a final one on this please
Can you give me the formula to count the number of cells in column H that fit this criteria i.e. the ones that turn orange
In this example H5 and H7 - total 2

 

[Shrikant]

Type/Copy this formula

=SUMPRODUCT(--($J$4:$J$13=7),--(ABS($J$1-$H4:$H13)=MIN(IF($J$4:$J$13=7,ABS($H$4:$H$13-$J$1)))))

Press Ctrl+Shift+Enter, not just Enter

 

[Peter_SSs]

.. or for a normal-entry formula, if you are using Excel 2010 or later:

=SUMPRODUCT(--(J4:J13=7),--(ABS(J1-H4:H13)=AGGREGATE(15,6,ABS(J1-H4:H13)/(J4:J13=7),1)))

 

[Orangeanorak]

Try this.
1. Select B4:B10 and H4:H10, ensuring either B4 or H4 is the active cell.
2. Apply the CF shown.

Excel Workbook
	B	C	D	E	F	G	H	I	J
1	*	*	*	*	*	*	*	*	17
2	*	*	*	*	*	*	*	*	*
3	*	*	*	*	*	*	*	*	*
4	Red	*	*	*	*	*	7	*	7
5	Black	*	*	*	*	*	8	*	7
6	Blue	*	*	*	*	*	17	*	3
7	Green	*	*	*	*	*	8	*	7
8	White	*	*	*	*	*	49	*	6
9	Brown	*	*	*	*	*	5	*	2
10	Grey	*	*	*	*	*	31	*	5
11	Scarlet	*	*	*	*	*	11	*	6
12	Yellow	*	*	*	*	*	13	*	5
13	Orange	*	*	*	*	*	22	*	1
14	*	*	*	*	*	*	*	*	*
CF Nearest with Condition

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4	1. / Formula is =AND($J4=7,ABS($J$1-$H4)=MIN(IF($J$4:$J$13=7,ABS($H$4:$H$13-$J$1))))	Abc

Sorry people - one more tweak if possible please
I need this CF to work when J1 is greater than zero

Thanks

 

[Peter_SSs]

I need this CF to work when J1 is greater than zero

Try..
=AND($J$1>0,$J4=7,ABS($J$1-$H4)=MIN(IF($J$4:$J$13=7,ABS($H$4:$H$13-$J$1))))


BTW, best not to quote whole longish posts as the default - it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.