Excel : Conditional formatting

4
votes

I have two columns,

    **COL-A**      **COL-B**  
1      419             393
2     2199            1513
3        3               3
4       23              20
5        3               3 
4     2964            2181

I want to have conditional formatting using color scales between each A1 B1, A2 B2, ...

Currently when compared, 419 is green and 393 is red (red-yellow-green).

But the color of column B needs to be based on ratio (393/419*100=93.79%, which should be closer to green, while 10% would be closer to be red).

Could you please help me to do this?

1
excelformattingexcel-2007conditional-formatting
I tried to edit your question to make it easier to understand. Please review my edits to make sure this is what you want. – Peter Lang
@Peter Lang yes.thanx for the formatting – Maju
This would be relatively easy to do in a macro. Are you okay with using visual basic instead of conditional formatting? – Evelyn
Using your example, B1 would be closer to green because its ratio is 93.79%. Are you saying that A1 should be closer to red specifically because its cell contains a value representing the remaining 6.21%? Or how exactly is the color of the A cells determined? – Nick Spreitzer
@Evelyn How to do that in vb macros? @BadNinja Thanks I managed to do that using some cell rules(instead of the colorscales). – Maju

1 Answers

2
votes

I think this does the trick. Conditional format for B2 in the image.

Perhaps a bit late :)

alt text

HTH!