Automate Conditional Formatting

DPChristman

Board Regular
Joined
Sep 4, 2012
Messages
126
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?


SALES #SALES $AVG SALERETURN #RETURN $AVG RET
DISTRICT 10100 1,000.00 10.002 200.00 100.00
ZONE 01600 6,000.00 10.0012 1,200.00 100.00

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

:confused:
 

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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?


SALES #SALES $AVG SALERETURN #RETURN $AVG RET
DISTRICT 10100 1,000.00 10.002 200.00 100.00
ZONE 01600 6,000.00 10.0012 1,200.00 100.00

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

:confused:
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:

DPChristman

Board Regular
Joined
Sep 4, 2012
Messages
126
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)
 

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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)
=$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?
 

DPChristman

Board Regular
Joined
Sep 4, 2012
Messages
126
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



Exception Threshold ($1,648.47)-3.8% 15$515.505.5%
295$9,353.48 1,041.06$42,826.36($1,268.05) 12$396.544.2%
861 861 861 861 861 860 860
253,722$8,053,347.84 896,351$36,873,496.41($1,091,793.40)-3.0% 10,120$341,022.084.2%
CASH SALESALL SALESDISCOUNTSCASH RETURN INFORMATION
Sales # Sales $ Sales # Sales $Total Discount Dollars Percent To Sales Cash Return # Cash Return Dollars% to Cash Sales $
394$12,473.09 1,815$77,814.96($2,208.43)-2.8% 13$363.312.9%
526$17,394.54 1,614$62,901.19($1,623.86)-2.6% 25$998.505.7%
361$13,179.52 1,518$70,751.42($2,391.83)-3.4% 10$331.962.5%
406$13,010.61 1,440$57,331.87($1,759.54)-3.1% 18$681.695.2%
1,687 56,057.76 6,387 268,799($7,983.66)-3.0% 66 2,375.464.2%
304$7,589.37 1,193$37,321.11($742.74)-2.0% 12$217.242.9%
455$14,657.09 1,523$61,749.18($2,750.81)-4.5% 14$535.873.7%
285$8,115.46 890$33,335.09($1,077.04)-3.2% 13$392.654.8%
300$9,751.61 1,051$38,919.32($1,346.96)-3.5% 8$303.773.1%
320$8,052.19 1,293$43,447.02($1,332.78)-3.1% 8$188.232.3%
1,664 48,165.72 5,950 214,772($7,250.33)-3.4% 55 1,637.763.4%

<colgroup><col span="9"><col></colgroup><tbody>
</tbody>




 

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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



Exception Threshold ($1,648.47)-3.8% 15$515.505.5%
295$9,353.48 1,041.06$42,826.36($1,268.05) 12$396.544.2%
861 861 861 861 861 860 860
253,722$8,053,347.84 896,351$36,873,496.41($1,091,793.40)-3.0% 10,120$341,022.084.2%
CASH SALESALL SALESDISCOUNTSCASH RETURN INFORMATION
Sales # Sales $ Sales # Sales $Total Discount Dollars Percent To Sales Cash Return # Cash Return Dollars% to Cash Sales $
394$12,473.09 1,815$77,814.96($2,208.43)-2.8% 13$363.312.9%
526$17,394.54 1,614$62,901.19($1,623.86)-2.6% 25$998.505.7%
361$13,179.52 1,518$70,751.42($2,391.83)-3.4% 10$331.962.5%
406$13,010.61 1,440$57,331.87($1,759.54)-3.1% 18$681.695.2%
1,687 56,057.76 6,387 268,799($7,983.66)-3.0% 66 2,375.464.2%
304$7,589.37 1,193$37,321.11($742.74)-2.0% 12$217.242.9%
455$14,657.09 1,523$61,749.18($2,750.81)-4.5% 14$535.873.7%
285$8,115.46 890$33,335.09($1,077.04)-3.2% 13$392.654.8%
300$9,751.61 1,051$38,919.32($1,346.96)-3.5% 8$303.773.1%
320$8,052.19 1,293$43,447.02($1,332.78)-3.1% 8$188.232.3%
1,664 48,165.72 5,950 214,772($7,250.33)-3.4% 55 1,637.763.4%

<colgroup><col span="9"><col></colgroup><tbody>
</tbody>




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
 

Forum statistics

Threads
1,082,333
Messages
5,364,675
Members
400,810
Latest member
elbashka

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top