Conditional Formatting Cells Using Values From Adjacent Cells

[GreenWizard]

Is there a way to apply conditional formatting to one Column, but reference the Adjacent Column for the values (highest to lowest)? See example below:

COLUMN 1	COLUMN 2
9	5
73	10
48	8
70	5
42	1
29	7
99	5
51	10
69	2
64	10

<tbody>
</tbody>

One can select Column 1 then click > 'Conditional Formatting' > 'Color Scales' > 'Green - Yellow - Red Color Scale'
And a color gradient will be applied to Column 1 where the highest value is Green and the lowest value is Red.

HOWEVER, how can you apply the same color gradient on Column 1, BUT reference Column 2 to determine the highest to lowest values??
In this example, values 73, 51, & 64 would get a Green background (highest value corresponding value = 10), while 42 would have a Red background (lowest value corresponding value = 1)???

Thanks in advance!!!

 

[oldbrewer]

in A1 put =B1=large($b$1$b$10,1)

if say b3 b5 b7 were all equal biggest then a3 a5 a7 would be colored

 

[oldbrewer]

then use small to get the lowest

 

[GreenWizard]

I'm sorry, I don't understand. Can you clarify?

Is A1 the Column Name or the first Cell (Value = 9). Also where do I put that formula? Do I put that formula in the cell or perhaps a New Formatting Rule Description.

Thanks!!!

 

[oldbrewer]

it is the cond formatting formula applied to A1 (whose value =9)

I have excel 2000 so can only apply a max of 3 rules to a cell so in this example I would mark the highest and lowest

 

[Fluff]

@oldbrewer
The OP is referring to Colour Scales, which you don't have in your version.
@GreenWizard
I'm not sure it's possible to do what you are asking for. (But may well be proved wrong)

 

[GreenWizard]

Okay thank you!

If not, do you know a way to convert (via formula or conditional formatting) a value into an image instead of a color gradient? For example, setting a value to become a Green Star or a Blue Star or different type of shape, instead of shading the background of a cell?

Thanks again!!!

 

[Fluff]

Have a look here https://www.contextures.com/xlCondFormat03.html#tempcolor & see if there is anything you can use

 

[oldbrewer]

you will likke this approach - Fluff.....

COLUMN 1	COLUMN 2					rank	vlookup				rank
9	5					6	36				1	4	green
73	10					1	4				2	43	pale green
48	8					4	8				3	20	pale cyan
70	5					6	36				4	8	cyan
42	1					10	3				5	5	blue
29	7					5	5				6	36	light yellow
99	5					6	36				7	6	yellow
51	10					1	4				8	45	light orange
69	2					9	46				9	46	orange
64	10					1	4				10	3	red
					this macro has coloured the cells in column A
					light yellow
					green
					cyan
					light yellow
					red
					blue
					light yellow
					green
					orange
					green
					Sub Macro4()
					'
					' Macro4 Macro
					' Macro recorded 06/08/2018 by bob
					'
					'
					For j = 2 To 11
					Cells(j, 1).Select
					With Selection.Interior
					.ColorIndex = Cells(j, 8)
					.Pattern = xlSolid
					End With
					Next j
					End Sub

<colgroup><col><col><col span="11"><col><col span="2"></colgroup><tbody>
</tbody>

 

[GreenWizard]

Good stuff guys! Thank you very much for your help.

I'm still looking for a native solution that doesn't require a macro, but the link Fluff provided is extremely helpful as is the macro oldbrewer wrote. Thank you!!!!