Conditional Formatting help

[prasad_inamdar]

Dear All,

I have one column of Site ID's sorted with respect to certain ratio.
Also I have Same Site ID sorted with another criteria say demand.

I would like to select or highlight site IDs with Ratio greater than 5 from sorting with demand column.

Can you please help with it?

E.g.

Site Ratio
1a 10
2b 6
3c 3
4d 2

Site Demand
3c 100
4d 80
1a 70
2b 60

I would like to highlight 1a and 2b from demand column as their ratio is > 5

 

[greenman]

you can use condition formatting with formula.

VLOOKUP($A2,$E$2:$F$5,2,FALSE)>5

 

[Marcol]

Is this what you need?
Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 77px"><COL style="WIDTH: 64px"><COL style="WIDTH: 195px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Site</TD><TD style="BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">ID Ratio</TD><TD></TD><TD style="BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Site</TD><TD style="BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">ID Demand</TD><TD></TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Conditional Formatting Proof</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">1a</TD><TD style="TEXT-ALIGN: right">10</TD><TD></TD><TD style="FONT-FAMILY: Verdana">3c</TD><TD style="TEXT-ALIGN: right">100</TD><TD></TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana">2b</TD><TD style="TEXT-ALIGN: right">6</TD><TD></TD><TD style="FONT-FAMILY: Verdana">4d</TD><TD style="TEXT-ALIGN: right">80</TD><TD></TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana">3c</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">1a</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">70</TD><TD></TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana">4d</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana">2b</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">60</TD><TD></TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Conditional Formatting Formula uses G2</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G2</TD><TD>=INDEX($A$2:$B$5,MATCH($D2,$A$2:$A$5,0),2)>5</TD></TR><TR><TD>G3</TD><TD>=INDEX($A$2:$B$5,MATCH($D3,$A$2:$A$5,0),2)>5</TD></TR><TR><TD>G4</TD><TD>=INDEX($A$2:$B$5,MATCH($D4,$A$2:$A$5,0),2)>5</TD></TR><TR><TD>G5</TD><TD>=INDEX($A$2:$B$5,MATCH($D5,$A$2:$A$5,0),2)>5</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Select the Range D2:E5
Conditional formatting > Use a formula .......

Hope this helps.

 

[p45cal]

Excel Workbook
	C	D	E	F	G
3	Site	Ratio
4	1a	10
5	2b	6
6	3c	3
7	4d	2
8
9				Site	Demand
10				3c	100
11				4d	80
12				1a	70
13				2b	60
Sheet30

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F10	1. / Formula is =VLOOKUP(F10,$C$4:$D$7,2,FALSE)>5	Abc

The conditional formatting (CF) above was added to all four cells F10:F13 at once by having all the 4 cells selected, making sure that the active cell while applying CF was F10. This is important. You should do similar.

 

[prasad_inamdar]

Thank you so much p45cal, Marcol, and greenman!
It was great help from all of you