conditional format one column base on rank specified in another column

Question

I am trying to conditionally format one column base on rank specified in another column in excel 2012.

consider the following example:

col_1  col_2

Alice   2

Sam     1

Tim     4

Alex    3

the highlighting color of column one should be determined by the value/rank in column 2, say the min in column 2 correspond to white and the max correspond to red, I want the first column to have corresponding highlighting. Alice - pale pink; Sam - white; Tim - red; Alex - dark pink. I do have lots of columns, so I do not want to have to make separate rules for all possible value/rank in column 2. I know there is a way to "format all cell base on their values" in excel, I am looking for the counter part "format all cell base on other cells' values"

score 1 · Answer 1 · edited May 23 '17 at 11:56

1

A very rudimentary option: Capture Change or Calculate events for the Worksheet, and copy the part of the format of choice, from the range in column B to the range in column A.

For copying the format:

Excel: Make conditional formatting static

or

http://www.mrexcel.com/forum/excel-questions/284013-convert-conditional-formatting-into-standard-one.html

edited May 23 '17 at 11:56

Community

1
1

answered Nov 14 '13 at 16:26

sancho.s ReinstateMonicaCellio

14,708
20
93
185

Thanks for the answer, Can you unpack a bit? How do I copy the format(I assume you mean the conditional format obtained from column 2)? – Cici Nov 14 '13 at 16:36
Updated. Hopefully that is what you were asking for. – sancho.s ReinstateMonicaCellio Nov 14 '13 at 17:23

score 0 · Answer 2 · answered Jul 25 '14 at 15:03

Thanks to sancho.s for reminding me about this thread I opened. I think sancho.s's answer will work, but I did not end up using it. but it did inspired my own solution. My solution is probably not that applicable for everyone else, but maybe worth mentioning here.

I ended up using the activeX excel server in matlab to edit the formatting. The reason I chose this method is because I am more comfortable with matlab than vb.

conditional format one column base on rank specified in another column

2 Answers2