Conditional gradient formatting per row

[50ld13r]

HI,

I want to do gradient formatting across the row for each row in my dataset. I have about 1000 rows so don't want to be going crazy with the format painter. It looks like I cant use relative references in the min/max formulas so was wondering how to do it. Ideally I want to avoid helper cells and VBA. Seems that it should be simple but cant figure it out.

Thanks

 

[Dan_W]

Hi.
My apologies but I'm not sure I understand what you mean about relative referencing not being possible in the min/max formulas - do you have an example of the formula you're trying to use? I came up with the following example, where: (a) gradients are used in conditional formatting; and (b) the formatting applies to the cell that references the highest or lowest values in column C, relative to its row. I've used two CF expressions, but they use the same formula.

I have included the XL2BB excerpt below for your reference - you will see that the gradient formatting didn't copy across, so I include a screen capture too. Is that kind of what you were looking for?

Book3
	C	D	E
3	20		Num1
4	154959		Num2
5	60000		Num3
6	153524		Num4
7	140983		Num5
8	500000		Num6
9	128287		Num7
Mr Excel

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:E9	Expression	=$C3=MIN($C$3:$C$9)	text	NO
E3:E9	Expression	=$C3=MAX($C$3:$C$9)	text	NO

 

[50ld13r]

No problem thanks for giving it a go - i will try explain the problem a little better. Below is a sample of the data - I have 1000s of rows so don't want to be setting this up for each row. Ideally I would like to do it without helper columns or VBA.

I tried using the following:

but got the error:

 

[Dan_W]

Ahh, I understand now. I see the problem. I've not been confronted with this before, so let me think about it. Can I just ask, though, why is VBA off-the-table? Is it because you need to share the file with others and don't want to have to deal with macro security issues, etc? I ask because one solution may be (off the top of my head) getting VBA to make the condtional formatting rules for you. It could still be conditionally formatted, and the final workbook wouldn't need to have any VBA... it's just a thought.

I'm not sure it would work though, because i wonder how Excel would fare under the stress of having to process thousands of CF rules everytime a cell changed, etc...
As it is, Excel just will not play when you hit it's upper limit on the number of fonts you have in your workbook... I'can't imagine it would like limitless CF rules either...

I'll think about it. Someone else might have the solution off the top of their head, but I'll look into it this weekend in any event.

 

[Fluff]

Unfortunately I think you have no option but to do it row by row.
Either via the format painter, or via VBA to setup the CF rule on each row

 

[Dan_W]

@Fluff - Thank you very much, Fluff. I figured that might be the case, but didn't want to throw in the towel too soon unless an MVP or some more experienced / knowledgable than me would be able to confirm one way or the other.

@50ld13r - I had a look into this and Fluff has confirmed above, that unfortunately - as you suspected - relative referencing won't work with colour scale conditional formatting. Depending on your needs, it seems as though your options are either: (a) use the format painter to manually apply the CF rule for one row to the rest of the rows on the spreadsheet; or (b) use VBA to undertake the task for you. I'm happy to help you with the latter if VBA is indeed a possibility.

 

[50ld13r]

Thanks for investigating - It does seem bit of a missed opportunity in functionality. Unfortunately the report will be client side which do not enable macros to their end users. They update the report monthly and the data then remains static for that duration which covers just shy of 3k rows. Looks like the only way will be to use helper cells. Might have to be a rank to help flatten out the extremes.

 

[Dan_W]

Sorry, I'm a bit confused - aren't you going to be applying the conditional formatting? Give me one second.

 

[50ld13r]

Yes I am but will have to use a 16 helper columns that will show a rank of the % data. This will be used to apply the formatting to the percentages. Regardless of what the % value is for the highest value it will all have the same rank and thus all coloured the same - high to low.

 

[Dan_W]

This is what you're after, is it not? I give you Exhibit A and Exhibit B. Both areas were originally populated by random numbers between 1 and 50, which I then adjusted to test whetehr the conditional formatting rules were working or not.

Exhibit A is non-referential. It started as a row for one rule, and then the scope of its application was expanded out across 12 columns and 18 rows. The consequence being that color scale is extended from between the lowest and highest values, wherever found in this 12x18 area.

I have highlighted four rows where I have intentionally warped the (otherwise random) values to give a series of 'extreme' values, but including within the row a clear high or low value relative to the other values in the row.