Conditional formatting - full-color color bar based on one column

Question

Suppose I want color full lines to be filled based on the values ​​in a column (using the excel option of the built-in color bar in the conditional formatting menu ). How do I achieve this? See Next Image

Answer 1

I found the Range.DisplayFormat.Interior.Color property in this post in Mrexcel. Using this property, I was able to get the color of the cell with conditional formatting and use it for other rows. Catch it works only since 2010. I have Excel 2010, so this worked for me. Here is the exact code -

 For i = rowStart To rowEnd For j = columnStart To columnEnd Cells(i, j).Interior.Color = Cells(i, 4).DisplayFormat.Interior.Color Next Next 

Answer 2

If I understood you correctly, I struggled with the same problem. That is, to format entire rows based on values ​​in a single column, where the values ​​were formatted using Excel color scales.

I found this really funny lightweight workaround that involves copying your color cells into a word and then back to excel, after which you can delete the values ​​and replace them with whatever values ​​you want, without changing the format

https://superuser.com/questions/973921/copy-conditional-formatting-3-color-scheme-to-another-tab/973974#973974?newreg=fc5ca6d04a5a406fa39cd4796b6a539e

All credits to Raystafarian

Answer 3

You really don't need VBA to do this.

Keep in mind that you cannot achieve the desired behavior with a single conditional formatting rule; You will need to have a separate rule for each line color definition based on sales. Secondly: I found that achieving the desired conditional formatting behavior in Excel is much simpler using Named Ranges for rules instead of regular formulas.

Given these issues, follow these steps to create your named range, and then create your conditional formatting rules.

• First, select the first sales cell on the sheet (top row)
• Then give the cell the name SALE. Do this by pressing Ctl + F3 or select Formulas → Name Manager on the ribbon. Then select New.. In Name: enter SALES and in Refers to: enter =$XN , where X is the column of the first sales cell and N is the line number. Press Enter .
• Now select the entire range of cells that you want to develop.
• Choose Home → Conditional Formatting → New Rule...
• Select Use a Formula to Determine Which Cells to Format and enter =SALES=number , where number is the number of sales that you want to call in color.
• Select Format and the Fill tab. Now you need to decide what background color you want for your selected sales number. You can also select other formatting options, such as font color, etc.
• Click OK, OK, OK. Repeat steps 3 through 6 for each selected sales / color combination. If you want the color "all sales to be less than X", in your rule you enter =SALES<number (<is "less"; you can also make <=, which is "less than OR equal to"), if you want the rule happened between two numbers, you can do =AND(SALES<=CEILING, SALES>=FLOOR) , where the ceiling and floor are the upper and lower boundaries. If you need a color for "all sales, big X", you can do =SALES>number .

EDIT:

To simplify the entry of conditional formulas, you can use the Stop, if there is truth function. Go to Home → Conditional Formatting → Manage Rules , and select This Worksheet from the drop-down menu. Now you will see a list of all the rules that apply to your sheet, and to the right of each rule there will be a checkbox "Stop, if it is right."

For each line color rule, select the Stop if true check box. Now your formulas can be like this (for example):

• =Sales>25 for the green rule
• =Sales>10 for the yellow rule
• =Sales>0 for the Red rule

Etc, instead:

• =AND(Sales>0,Sales<=10) for the Red rule
• =AND(Sales>10,Sales<=25) for the yellow rule
• =Sales>25 for the green rule

The Stop If True box means that once a formatting rule has been applied to a cell, that cell will not be formatted again based on any other rules that apply to it. Note that this means that the order of the rules matters when using Stop If True .

Answer 4

You can do this with the standard conditional formatting menu; there is no need for VBA. You choose the option of specifying your own formula, and you can refer to a cell (block the column with "$") that is different from the one you want to highlight.

Background reading