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]

Sorry for the confusion, Dan. I used your code and that worked for me.

My inquiry was to know how I may customize it more, such as adding one more color. In your script, there are only two - low and high. I would like to have a middle color.

About the application of formatting only in respect of each row - this works perfectly. The only odd thing I noticed is that when for instance I have 5 numbers in a row and all of them are 12000, all the numbers are colored with the UpperColour, rather than the LowerColour. If there are no other numbers in the row, 12000 in this case should be lower. This is not an issue with what I'm doing right now but would be great to know how to alter.

All in all, considering the absence of such feature in Excel - conditional formatting only in respect of each row - what you have shared helped a lot. Thank you.

Of course, my apologies, I just remembered you had originally explained that. I'm not quite sure how to go about setting all values to the lowercolor, but let me take a crack at it and I'll update you.

 

[jdellasala]

I kind of just answered this question here, but I understand not wanting to click on every row, and I've found an even easier way.
Apply the color scale conditional formatting to the first row, then select those items and do a regular Copy. Now select of the remaining range and select Paste -> Formatting (be sure to use the Format Only or go to the Paste Special... dialog)

or

This is far easier to do than using a Macro, especially for multiple files.

 

[Fluff]

As mentioned in the other thread that method does not work.

 

[Crane]

Of course, my apologies, I just remembered you had originally explained that. I'm not quite sure how to go about setting all values to the lowercolor, but let me take a crack at it and I'll update you.

Thank you, Dan.

Actually, just having additional color is more than enough. How do I go about this? What are the lines I change? I tried adding additional color but I get an error. I have no knowledge of scripting at all. I just copy the color set then change it, but that did not work. I guess other parameters will have to be updated, just not sure which.

 

[Crane]

this

I kind of just answered this question here, but I understand not wanting to click on every row, and I've found an even easier way.
Apply the color scale conditional formatting to the first row, then select those items and do a regular Copy. Now select of the remaining range and select Paste -> Formatting (be sure to use the Format Only or go to the Paste Special... dialog)
View attachment 89308 or View attachment 89309
This is far easier to do than using a Macro, especially for multiple files.

I kind of just answered this question here, but I understand not wanting to click on every row, and I've found an even easier way.
Apply the color scale conditional formatting to the first row, then select those items and do a regular Copy. Now select of the remaining range and select Paste -> Formatting (be sure to use the Format Only or go to the Paste Special... dialog)
View attachment 89308 or View attachment 89309
This is far easier to do than using a Macro, especially for multiple files.

This doesn't work. The comparison, if you do this, still takes the whole range rather than individually per row.