Code to highlight cell in row that meets specific criteria

[Ranger32195]

Hi, I am looking for code that will highlight specific cells per row that meets certain criteria. For example, I have a table that looks like this

	GOLDMAN	MORGAN STANLEY	UBS	JP MORGAN
	LONG	SHORT	TOTAL	LONG	SHORT	TOTAL	LONG	SHORT	TOTAL	LONG	SHORT	TOTAL
HONG KONG	0.87	-0.33	-1.21	0.87	0.07	-0.80	0.87	0.07	-0.80	0.47	-0.33	-0.80
JAPAN	0.33	-0.33	-0.66	0.39	-0.31	-0.70	0.39	-0.30	-0.69	0.39	-0.34	-0.73
SINGAPORE	1.12	0.11	-1.00	1.12	0.32	-0.80	1.05	0.32	-0.74	1.17	0.32	-0.85
TAIWAN (USD)	1.18	0.31	-0.87	1.28	0.38	-0.90	1.28	0.38	-0.90	1.28	0.26	-1.02
FRANCE	-0.04	-0.65	-0.61	-0.02	-0.67	-0.65	-0.02	-0.67	-0.65	-0.02	-0.65	-0.63
GERMANY	-0.04	-0.65	-0.61	-0.02	-0.67	-0.65	-0.02	-0.67	-0.65	-0.02	-0.65	-0.63
ITALY	-0.04	-0.65	-0.61	-0.02	-0.67	-0.65	-0.02	-0.67	-0.65	-0.02	-0.65	-0.63
NETHERLANDS	-0.04	-0.65	-0.61	-0.02	-0.67	-0.65	-0.02	-0.67	-0.65	-0.02	-0.65	-0.63
BELGIUM	-0.04	-0.65	-0.61	-0.02	-0.67	-0.65	-0.02	-0.67	-0.65	-0.02	-0.65	-0.63
DENMARK	0.08	-0.75	-0.83	0.03	-0.62	-0.65	0.03	-0.62	-0.65	0.08	-0.71	-0.79
SPAIN	-0.04	-0.65	-0.61	-0.02	-0.67	-0.65	-0.02	-0.67	-0.65	-0.02	-0.70	-0.68
CANADA	1.33	0.19	-1.14	0.85	0.20	-0.65	1.28	0.63	-0.65	1.33	0.20	-1.13
AUSTRALIA	#VALUE!	1.10	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
CHINA A (USD)	1.53	#VALUE!	#VALUE!	1.38	#VALUE!	#VALUE!	1.38	#VALUE!	#VALUE!	1.28	#VALUE!	#VALUE!
INDIA (USD)	1.53	#VALUE!	#VALUE!	1.53	0.26	-1.27	1.53	#VALUE!	#VALUE!	1.53	#VALUE!	#VALUE!
SOUTH KOREA (USD)	1.18	0.26	-0.92	1.18	0.38	-0.80	1.28	0.38	-0.90	1.28	0.26	-1.02
BRAZIL (USD)	1.28	0.16	-1.12	1.28	0.38	-0.90	1.28	0.38	-0.90	1.53	0.16	-1.37
MEXICO (USD)	1.28	0.16	-1.12	1.28	0.38	-0.90	1.28	0.38	-0.90	1.53	0.16	-1.37
SOUTH AFRICA	7.73	6.47	-1.26	7.72	6.22	-1.50	8.23	6.20	-2.03	7.83	6.33	-1.50
SWEDEN	-0.17	-0.90	-0.73	-0.17	-0.82	-0.65	-0.17	-0.85	-0.68	-0.12	-0.85	-0.73
SWITZERLAND	-0.44	-1.09	-0.65	-0.44	-1.09	-0.65	-0.54	-1.19	-0.65	-0.44	-1.09	-0.65
UK	0.61	-0.04	-0.65	0.61	-0.04	-0.65	0.56	-0.09	-0.65	0.56	-0.09	-0.65
USA	1.03	0.41	-0.62	1.13	0.48	-0.65	1.13	0.48	-0.65	1.13	0.36	-0.77

<tbody>
</tbody>

for each cpty I'd like to highlight per country who has the cheapest long rate per row, cheapest short rate per row and cheapest overall rate

For example, Hong Kong Long will highlight JPMorgan 0.47, short will highlight UBS & Morgan Stanley as they have both 0.07

Any code would be greatly appreciated

 

[Scott T]

short will highlight UBS & Morgan Stanley as they have both 0.07

Since GOLDMAN has -.33 in short I assume you only want positive numbers highlighted.

	A	B	C
1		Goldman
2		Long	Short
3	Hong Kong	.87	-.33
4	Japan	.33	-.33

<tbody>
</tbody>

Select your rates the starting at B3 so that B3 is the active cell in the range
In Conditional Formatting select New Rule, Use A formula to determine which cells to Format
for Long use and select your formatting

=AND(B3=MIN($B3,$E3,$H3,$K3),B3>0)

Repeat the above for short but use

=AND(B3=MIN($C3,$F3,$I3,$L3),B3>0)

 

[Ranger32195]

Hi, thanks for that. I cannot get that to work unfortunately. Do I need to select each cell its to be applied to?

 

[Scott T]

You should select all the rates.

You would need to change the formula to match your data so the formula for short
is where C3, F3,I3,L3 have the rates for short in the top row and B3 is the top left corner of your rates.

=AND(B3=MIN($C3,$F3,$I3,$L3),B3>0)

Like wise you would need to change the references in side the min of the formula for Long to refer to the cells that have the long rates