Automate Conditional Formatting

DPChristman

DPChristman

Board Regular
Joined
Sep 4, 2012
Messages
191
Office Version
  1. 365
Platform
  1. Windows
I have a large spreadsheet that consists of 250 Districts, and about 25 Zones.

On this spreadsheet I am capturing totals by District and Zone.

Based on an individual cell, can I set a conditional format (color) for that entire row.

Example below:
I want all my district totals to be shaded one color, and my zone totals shaded another color.

I know that I can do this by applying the data filter, show only rows with the word District in it, and shade accordingly. Then do the same with zones.

The problem is doing them row by row is painstakingly annoying, and if I just highlight the group of 250 district that I have filtered, the conditional format applies itself to the individual stores in the districts. Each of these stores has it's own conditional format based on their exceeding positive or negative thresholds.

What I want to do is shade the cells for DISTRICT and ZONE, without impacting the other cells. Anyone have any ideas?


[TABLE="width: 471"]
<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]SALES #[/TD]
[TD]SALES $[/TD]
[TD]AVG SALE[/TD]
[TD]RETURN #[/TD]
[TD]RETURN $[/TD]
[TD]AVG RET[/TD]
[/TR]
[TR]
[TD]DISTRICT 10[/TD]
[TD="align: right"]100[/TD]
[TD] 1,000.00[/TD]
[TD] 10.00[/TD]
[TD="align: right"]2[/TD]
[TD] 200.00[/TD]
[TD] 100.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ZONE 01[/TD]
[TD="align: right"]600[/TD]
[TD] 6,000.00[/TD]
[TD] 10.00[/TD]
[TD="align: right"]12[/TD]
[TD] 1,200.00[/TD]
[TD] 100.00[/TD]
[/TR]
</tbody>[/TABLE]

:confused:
 
Sort by date Sort by votes
DPChristman said:
I have a large spreadsheet that consists of 250 Districts, and about 25 Zones.

On this spreadsheet I am capturing totals by District and Zone.

Based on an individual cell, can I set a conditional format (color) for that entire row.

Example below:
I want all my district totals to be shaded one color, and my zone totals shaded another color.

I know that I can do this by applying the data filter, show only rows with the word District in it, and shade accordingly. Then do the same with zones.

The problem is doing them row by row is painstakingly annoying, and if I just highlight the group of 250 district that I have filtered, the conditional format applies itself to the individual stores in the districts. Each of these stores has it's own conditional format based on their exceeding positive or negative thresholds.

What I want to do is shade the cells for DISTRICT and ZONE, without impacting the other cells. Anyone have any ideas?


[TABLE="width: 471"]
<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]SALES #[/TD]
[TD]SALES $[/TD]
[TD]AVG SALE[/TD]
[TD]RETURN #[/TD]
[TD]RETURN $[/TD]
[TD]AVG RET[/TD]
[/TR]
[TR]
[TD]DISTRICT 10[/TD]
[TD="align: right"]100[/TD]
[TD] 1,000.00[/TD]
[TD] 10.00[/TD]
[TD="align: right"]2[/TD]
[TD] 200.00[/TD]
[TD] 100.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ZONE 01[/TD]
[TD="align: right"]600[/TD]
[TD] 6,000.00[/TD]
[TD] 10.00[/TD]
[TD="align: right"]12[/TD]
[TD] 1,200.00[/TD]
[TD] 100.00[/TD]
[/TR]
</tbody>[/TABLE]

:confused:
Click to expand...

select cell A2. You will be making all of your rules for this cell alone.

in the CD select new rule then select Use a Formula to determine which cells to format

in the box type the following

Code: 
=$A2="District"

select your color. rinse and repeat the steps for each different color you want.

once you have all your rules. Use format Painter (little paint brush in the clipboard section of the home ribbon) and paint all the cells you want to follow your rules. the $A will ensure they check against Column A cell.
 
Last edited:
Upvote 0
I think you lost me there.

I know what format painter is.

I don't understand what you mean my =$A2="District"

add the conditional format to the cells in a single row where only on cell contains the word district.

Doing a standard past doesn't work because it includes the threshold based formatting in the district totals. Since the combination of all stores in the district will exceed the threshold, it will always be highlighted (but shouldn't be)
 
Upvote 0
DPChristman said:
I think you lost me there.

I know what format painter is.

I don't understand what you mean my =$A2="District"

add the conditional format to the cells in a single row where only on cell contains the word district.

Doing a standard past doesn't work because it includes the threshold based formatting in the district totals. Since the combination of all stores in the district will exceed the threshold, it will always be highlighted (but shouldn't be)
Click to expand...

=$A2="District" is the formula you enter into the conditional formatting.

I am not sure we are on the same page with this. why would you not want all the stores with district in column A to be highlighted?
 
Upvote 0
There are exception thresholds set for individual stores. Stores are highlighted when that store exceeds the thresholds. For example: average return threshold in the company may be $31.00. Any store in excess of that threshold is highlighted. That's the way it should work. However, if there are 4 stores in a district, and their combined returns exceed $31, that district would get highlighted as well. However, $31 for the district does not exceed the threshold. When we deal in percent to sales, it is not an issue if the threshold highlights or not because % to sales is percent to sales, but in unit counts or dollars, the cells are highlighted in error



[TABLE="width: 1684"]
<colgroup><col span="9"><col></colgroup><tbody>[TR]
[TD="colspan: 2"] Exception Threshold [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]($1,648.47)[/TD]
[TD="align: right"]-3.8%[/TD]
[TD] 15[/TD]
[TD]$515.50[/TD]
[TD="align: right"]5.5%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 295[/TD]
[TD]$9,353.48[/TD]
[TD] 1,041.06[/TD]
[TD]$42,826.36[/TD]
[TD]($1,268.05)[/TD]
[TD] [/TD]
[TD] 12[/TD]
[TD]$396.54[/TD]
[TD="align: right"]4.2%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 861[/TD]
[TD] 861[/TD]
[TD] 861[/TD]
[TD] 861[/TD]
[TD] 861[/TD]
[TD][/TD]
[TD] 860[/TD]
[TD] 860[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 253,722[/TD]
[TD]$8,053,347.84[/TD]
[TD] 896,351[/TD]
[TD]$36,873,496.41[/TD]
[TD]($1,091,793.40)[/TD]
[TD="align: right"]-3.0%[/TD]
[TD] 10,120[/TD]
[TD="align: right"]$341,022.08[/TD]
[TD="align: right"]4.2%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]CASH SALES[/TD]
[TD="colspan: 2"]ALL SALES[/TD]
[TD="colspan: 2"]DISCOUNTS[/TD]
[TD="colspan: 3"]CASH RETURN INFORMATION[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Sales # [/TD]
[TD]Sales $[/TD]
[TD] Sales # [/TD]
[TD]Sales $[/TD]
[TD]Total Discount Dollars[/TD]
[TD] Percent To Sales [/TD]
[TD] Cash Return # [/TD]
[TD]Cash Return Dollars[/TD]
[TD]% to Cash Sales $[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 394[/TD]
[TD]$12,473.09[/TD]
[TD] 1,815[/TD]
[TD]$77,814.96[/TD]
[TD]($2,208.43)[/TD]
[TD]-2.8%[/TD]
[TD] 13[/TD]
[TD]$363.31[/TD]
[TD]2.9%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 526[/TD]
[TD]$17,394.54[/TD]
[TD] 1,614[/TD]
[TD]$62,901.19[/TD]
[TD]($1,623.86)[/TD]
[TD]-2.6%[/TD]
[TD] 25[/TD]
[TD]$998.50[/TD]
[TD]5.7%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 361[/TD]
[TD]$13,179.52[/TD]
[TD] 1,518[/TD]
[TD]$70,751.42[/TD]
[TD]($2,391.83)[/TD]
[TD]-3.4%[/TD]
[TD] 10[/TD]
[TD]$331.96[/TD]
[TD]2.5%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 406[/TD]
[TD]$13,010.61[/TD]
[TD] 1,440[/TD]
[TD]$57,331.87[/TD]
[TD]($1,759.54)[/TD]
[TD]-3.1%[/TD]
[TD] 18[/TD]
[TD]$681.69[/TD]
[TD]5.2%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 1,687[/TD]
[TD] 56,057.76[/TD]
[TD] 6,387[/TD]
[TD] 268,799[/TD]
[TD="align: right"]($7,983.66)[/TD]
[TD="align: right"]-3.0%[/TD]
[TD] 66[/TD]
[TD] 2,375.46[/TD]
[TD]4.2%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 304[/TD]
[TD]$7,589.37[/TD]
[TD] 1,193[/TD]
[TD]$37,321.11[/TD]
[TD]($742.74)[/TD]
[TD]-2.0%[/TD]
[TD] 12[/TD]
[TD]$217.24[/TD]
[TD]2.9%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 455[/TD]
[TD]$14,657.09[/TD]
[TD] 1,523[/TD]
[TD]$61,749.18[/TD]
[TD]($2,750.81)[/TD]
[TD]-4.5%[/TD]
[TD] 14[/TD]
[TD]$535.87[/TD]
[TD]3.7%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 285[/TD]
[TD]$8,115.46[/TD]
[TD] 890[/TD]
[TD]$33,335.09[/TD]
[TD]($1,077.04)[/TD]
[TD]-3.2%[/TD]
[TD] 13[/TD]
[TD]$392.65[/TD]
[TD]4.8%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 300[/TD]
[TD]$9,751.61[/TD]
[TD] 1,051[/TD]
[TD]$38,919.32[/TD]
[TD]($1,346.96)[/TD]
[TD]-3.5%[/TD]
[TD] 8[/TD]
[TD]$303.77[/TD]
[TD]3.1%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 320[/TD]
[TD]$8,052.19[/TD]
[TD] 1,293[/TD]
[TD]$43,447.02[/TD]
[TD]($1,332.78)[/TD]
[TD]-3.1%[/TD]
[TD] 8[/TD]
[TD]$188.23[/TD]
[TD]2.3%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 1,664[/TD]
[TD] 48,165.72[/TD]
[TD] 5,950[/TD]
[TD] 214,772[/TD]
[TD="align: right"]($7,250.33)[/TD]
[TD="align: right"]-3.4%[/TD]
[TD] 55[/TD]
[TD] 1,637.76[/TD]
[TD]3.4%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




media_set
 
Upvote 0
DPChristman said:
There are exception thresholds set for individual stores. Stores are highlighted when that store exceeds the thresholds. For example: average return threshold in the company may be $31.00. Any store in excess of that threshold is highlighted. That's the way it should work. However, if there are 4 stores in a district, and their combined returns exceed $31, that district would get highlighted as well. However, $31 for the district does not exceed the threshold. When we deal in percent to sales, it is not an issue if the threshold highlights or not because % to sales is percent to sales, but in unit counts or dollars, the cells are highlighted in error



[TABLE="width: 1684"]
<colgroup><col span="9"><col></colgroup><tbody>[TR]
[TD="colspan: 2"] Exception Threshold [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]($1,648.47)[/TD]
[TD="align: right"]-3.8%[/TD]
[TD] 15[/TD]
[TD]$515.50[/TD]
[TD="align: right"]5.5%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 295[/TD]
[TD]$9,353.48[/TD]
[TD] 1,041.06[/TD]
[TD]$42,826.36[/TD]
[TD]($1,268.05)[/TD]
[TD] [/TD]
[TD] 12[/TD]
[TD]$396.54[/TD]
[TD="align: right"]4.2%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 861[/TD]
[TD] 861[/TD]
[TD] 861[/TD]
[TD] 861[/TD]
[TD] 861[/TD]
[TD][/TD]
[TD] 860[/TD]
[TD] 860[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 253,722[/TD]
[TD]$8,053,347.84[/TD]
[TD] 896,351[/TD]
[TD]$36,873,496.41[/TD]
[TD]($1,091,793.40)[/TD]
[TD="align: right"]-3.0%[/TD]
[TD] 10,120[/TD]
[TD="align: right"]$341,022.08[/TD]
[TD="align: right"]4.2%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]CASH SALES[/TD]
[TD="colspan: 2"]ALL SALES[/TD]
[TD="colspan: 2"]DISCOUNTS[/TD]
[TD="colspan: 3"]CASH RETURN INFORMATION[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Sales # [/TD]
[TD]Sales $[/TD]
[TD] Sales # [/TD]
[TD]Sales $[/TD]
[TD]Total Discount Dollars[/TD]
[TD] Percent To Sales [/TD]
[TD] Cash Return # [/TD]
[TD]Cash Return Dollars[/TD]
[TD]% to Cash Sales $[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 394[/TD]
[TD]$12,473.09[/TD]
[TD] 1,815[/TD]
[TD]$77,814.96[/TD]
[TD]($2,208.43)[/TD]
[TD]-2.8%[/TD]
[TD] 13[/TD]
[TD]$363.31[/TD]
[TD]2.9%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 526[/TD]
[TD]$17,394.54[/TD]
[TD] 1,614[/TD]
[TD]$62,901.19[/TD]
[TD]($1,623.86)[/TD]
[TD]-2.6%[/TD]
[TD] 25[/TD]
[TD]$998.50[/TD]
[TD]5.7%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 361[/TD]
[TD]$13,179.52[/TD]
[TD] 1,518[/TD]
[TD]$70,751.42[/TD]
[TD]($2,391.83)[/TD]
[TD]-3.4%[/TD]
[TD] 10[/TD]
[TD]$331.96[/TD]
[TD]2.5%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 406[/TD]
[TD]$13,010.61[/TD]
[TD] 1,440[/TD]
[TD]$57,331.87[/TD]
[TD]($1,759.54)[/TD]
[TD]-3.1%[/TD]
[TD] 18[/TD]
[TD]$681.69[/TD]
[TD]5.2%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 1,687[/TD]
[TD] 56,057.76[/TD]
[TD] 6,387[/TD]
[TD] 268,799[/TD]
[TD="align: right"]($7,983.66)[/TD]
[TD="align: right"]-3.0%[/TD]
[TD] 66[/TD]
[TD] 2,375.46[/TD]
[TD]4.2%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 304[/TD]
[TD]$7,589.37[/TD]
[TD] 1,193[/TD]
[TD]$37,321.11[/TD]
[TD]($742.74)[/TD]
[TD]-2.0%[/TD]
[TD] 12[/TD]
[TD]$217.24[/TD]
[TD]2.9%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 455[/TD]
[TD]$14,657.09[/TD]
[TD] 1,523[/TD]
[TD]$61,749.18[/TD]
[TD]($2,750.81)[/TD]
[TD]-4.5%[/TD]
[TD] 14[/TD]
[TD]$535.87[/TD]
[TD]3.7%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 285[/TD]
[TD]$8,115.46[/TD]
[TD] 890[/TD]
[TD]$33,335.09[/TD]
[TD]($1,077.04)[/TD]
[TD]-3.2%[/TD]
[TD] 13[/TD]
[TD]$392.65[/TD]
[TD]4.8%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 300[/TD]
[TD]$9,751.61[/TD]
[TD] 1,051[/TD]
[TD]$38,919.32[/TD]
[TD]($1,346.96)[/TD]
[TD]-3.5%[/TD]
[TD] 8[/TD]
[TD]$303.77[/TD]
[TD]3.1%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 320[/TD]
[TD]$8,052.19[/TD]
[TD] 1,293[/TD]
[TD]$43,447.02[/TD]
[TD]($1,332.78)[/TD]
[TD]-3.1%[/TD]
[TD] 8[/TD]
[TD]$188.23[/TD]
[TD]2.3%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 1,664[/TD]
[TD] 48,165.72[/TD]
[TD] 5,950[/TD]
[TD] 214,772[/TD]
[TD="align: right"]($7,250.33)[/TD]
[TD="align: right"]-3.4%[/TD]
[TD] 55[/TD]
[TD] 1,637.76[/TD]
[TD]3.4%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




media_set
Click to expand...

You are kinda going further out than your original post.

I might be able to help with these if you list all the times you want something colored.

So far we have District & Zone, each a different color

Now we want to add a new color (I assume for each) when a threshold is breached. How many thresholds are you planning on tracking by color?

regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Conditional formatting: color rows with with shade when rows have the same value in their respective first cell
Replies
6
Views
555
Peter_SSs
Peter_SSs
A
  • Solved
Conditional Formatting - apply rules to multiple rows based on specific cells (drop down menus)
Replies
4
Views
1K
AndyB86
A
M
  • Question Question
Macro/Automating Edits and Calculations for Large Report (40K rows)
Replies
6
Views
243
kevin9999
K
S
  • Question Question
Conditional Formatting - How to Properly Copy + Paste without Duplicating Rules
Replies
1
Views
4K
6StringJazzer
6StringJazzer
G
  • Question Question
Looking to create conditional formatting using a reference column.
Replies
1
Views
498
6StringJazzer
6StringJazzer

Share this page

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
Back
Top