Conditional Formatting a Data Matrix

**fearonc** · 01-10-2016, 02:48 PM

I cannot seem to find a relevant search result for my puzzler. I have a row A2:E2 with different whole numbers (variables) in each of the five cells. I want to match any of the five numbers with all entries in the range A4:E20. If any match is found, I want the match in the range to have be filled green. I can accomplish this using a single data source cell from Row 2 but Conditional Formatting only appears to allow three rules, so I cannot capture all five cells of input individually using that method. I have attached file, if that helps. Many thanks!

**etaf** · 01-10-2016, 02:56 PM

you can do more than 3 rules in excel 2010
Also you maybe able to use an OR()

=OR(A4=$A$2, A4=$B$2,A4=$C$2,A4=$D$2,A4= $E$2)

what exactly are you doing with column F = PB ?

See attached using one rule for
A4:E10 matrix

**Pete_UK** · 01-10-2016, 03:02 PM

Select the cells from A4 to E20, then click on Conditional Formatting | Manage Rules and then delete the rule you have set up. Click on New Rule | Use a formula... , and put this formula in the dialogue box:

=ISNUMBER(MATCH(A4,$A$2:$E$2,0))

Click the Format button, then the Fill tab and choose green, then OK your way out. Excel will automatically adjust the cell references to suit the cells that are selected.

Hope this helps.

Pete

**AlKey** · 01-10-2016, 03:10 PM

With COUNTIF

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E
1	1	2	3	4	5
2	16	19	32	34	57
3
4	5	12	15	23	46
5	27	30	55	56	69
6	10	20	31	34	67
7	7	16	35	39	55
8	4	13	32	53	68
9	8	18	31	48	51
10	5	23	39	48	65

Sheet: Powerball

**fearonc** · 01-10-2016, 03:48 PM

Thanks all for the solution options. Kudos to all!!

Conditional Formatting a Data Matrix

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Formatting a Data Matrix

Re: Conditional Formatting a Data Matrix

Re: Conditional Formatting a Data Matrix

Re: Conditional Formatting a Data Matrix

Re: Conditional Formatting a Data Matrix

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Risk Matrix Conditional Formatting

Training matrix traffic lights conditional formatting

Conditional Formatting Training Matrix Dates

[SOLVED] Automatic Conditional Formatting in matrix of information

[SOLVED] Formatting columnar data to a monthly matrix

Bookmarks

Bookmarks

Posting Permissions

	A	B	C	D	E
1	1	2	3	4	5
2	16	19	32	34	57
3
4	5	12	15	23	46
5	27	30	55	56	69
6	10	20	31	34	67
7	7	16	35	39	55
8	4	13	32	53	68
9	8	18	31	48	51
10	5	23	39	48	65

	A	B	C	D	E
1	1	2	3	4	5
2	16	19	32	34	57
3
4	5	12	15	23	46
5	27	30	55	56	69
6	10	20	31	34	67
7	7	16	35	39	55
8	4	13	32	53	68
9	8	18	31	48	51
10	5	23	39	48	65

	A	B	C	D	E
1	1	2	3	4	5
2	16	19	32	34	57
3
4	5	12	15	23	46
5	27	30	55	56	69
6	10	20	31	34	67
7	7	16	35	39	55
8	4	13	32	53	68
9	8	18	31	48	51
10	5	23	39	48	65