Highlighting cells based on contents

[andrewb90]

I am looking for a formula to use in the conditional formatting to change a few cells background color based on the contents of a single cell. I am hoping to take data from say cells D2,4,6,810,12...etc and find the three largest totals and have the row they are in highlight. Anybody have ideas?

Thanks,
Andrew

 

[PATSYS]

Highlight rows 2 to 12, with row 2 bwing the active selection.

Go to FORMAT, CONDITIONAL FORMATTING, FORMULA IS, and type this formula:

=RANK($D2,$D$2:$D$12)<=3

Choose the desired shade.

 

[andrewb90]

That works really well, is there a way to have each rank a different color? Or would I have to do separate conditions for that?

Thanks,

Andrew

 

[PATSYS]

Yes it is possible

Condition 1
=RANK($D2,$D$2:$D$12)=1

Choose desired shade

Condition 2
=RANK($D2,$D$2:$D$12)=2

Choose desired shade

Condition 3
=RANK($D2,$D$2:$D$12)=3

Choose desired shade

 

[andrewb90]

Cool it works just fine! However I would like to have some of the cells highlighted that share the row with the colored cell. So if D2, D6, and D10 were my top three that were colored, I would like A,B, and C in each of those rows to be highlighted as well. Is this doable?

Thanks,

Andrew

 

[andrewb90]

Also if I wanted to change my formula to see the lowest 3 scores, what would i change? I might want both the highest and lowest scores to be highlighted.

 

[crook_101]

Hi,

Using RANK may cause issues if you have duplicates, suggest that you use the following:

=$D2>=LARGE($D$2:$D$12,3)

=$D2<=SMALL($D$2:$D$12,3)

To achieve the top 3, adjust the "3" above for more filled values

Excel Workbook
	D
2	1
3	4
4	5
5	67
6	8
7	9
8	0
9	7
10	32
11	1
12	3
Sheet1

Hope this helps
Ian.

 

[andrewb90]

Thanks! How would I color the cells in the same rows in columns ABC as well?

 

[crook_101]

Hi again,

Just saw your 2nd last post, yes this is doable

Before applying the format, highlight the ranges:

A2:A12 and D2:D12 (or whatever your ranges are), make sure that D2 is the active cell in the selection, then apply the CF I showed above.

This will give you:

Excel Workbook
	A	B	C	D
1	Header 1	Header 2	Header 3	Header 4
2				1
3				4
4				5
5				67
6				8
7				9
8				0
9				7
10				4
11				1
12				3
Sheet1

Cheers,
Ian