Search for range of values in column and select them

AggyRJ

New Member
Joined
Mar 29, 2013
Messages
14
I am at the end of a long macro I have been working on (thank you for all of those who have helped) and on the final page which sumarizes all of the information I would like to apply some conditional formatting to help the audience see what is going on. I have a column of percentages that start at J41 and run down several thousand rows (total amount will vary each time this report is run). The percentages are sorted so that they start with the "highest" negative percentage, runs through zero and goes up to the highest positive percentage. I would like to apply color scales using conditional formatting but the exact type that I need does not seem to be available so I need to work around it by searching the column for a specific range of values and applying the formatting to that range. Basically, what I need to do is apply the "Green, Yellow, Red Color Scale" to all values under 0, , fill solid green to all values equal to 0, then the "Red, Yellow, Green Color Scale" to all values above 0.

What I think would work best is a command which searched the column starting at J41 and selected all values >0, then applied the three color scale of Green, Yellow, Red; then searched for all values equal to 0 and filled the cells with solid green, then searched for all values above 0 and applied the three color scale of Red, Yellow, Green. I can do a Record Macro to get the steps for the formatting, but I do not know how to search a column for a range of values and select them to apply the formatting to. The end result should start at red for the most extreme negative percantage and gradually turn to a light green as it approaches zero, then zero itself would be solid green, then all values above zero would start out at a light green and progressively go to red. Any help would be appreciated.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top