Conditional formatting (or some other method)...

wardnine

Board Regular
Joined
Feb 27, 2004
Messages
56
I have this table:
sampletable.xls
ABCDEFGHIJKL
4STATEREVENUESEXPENDITURES
5
6TOTALFEDERALSTATELOCALTOTALCURRENTCAPITALOTHERDEBTASSETS
7SPENDINGOUTLAY
8
9United States................................................................6,912,961,561577,851,9863,384,155,7082,950,953,8677,120,866,0016,120,917,568792,997,957206,950,4763,953,662,9091,993,337,830
10
11Alabama........................................................................81,541,9858,912,95346,581,60226,047,43083,152,31874,782,7906,786,7801,582,74835,611,35221,933,387
12Alaska...........................................................................22,387,3844,072,94112,769,9255,544,51825,281,53720,932,6843,877,979470,87410,764,564-
13Arizona...........................................................................101,822,08311,627,09045,728,27544,466,71897,501,50679,741,24213,574,8794,185,38563,671,14625,183,287
14Arkansas........................................................................50,684,4515,965,16837,591,0757,128,20850,721,47245,719,6253,881,7471,120,10127,844,45217,986,140
15California........................................................................910,115,23190,991,784527,798,926291,324,521901,331,175772,186,053115,262,19413,882,929383,017,122418,888,262
16
17Colorado.........................................................................98,311,9406,283,43842,628,93849,399,563104,315,58987,213,28012,891,7254,210,58385,991,16144,575,911
18Connecticut....................................................................107,348,5155,510,71638,965,84662,871,953103,711,62593,028,7808,475,9592,206,88634,875,054495,775
19Delaware........................................................................18,960,8901,444,18012,473,2105,043,49920,067,26917,192,3792,688,578186,3125,125,5951,562,590
20District of Columbia..........................................................17,490,7582,395,804-15,094,95317,363,61914,313,4553,050,165---
21Florida............................................................................306,875,68530,721,807136,419,514139,734,365316,828,229263,541,81745,908,9047,377,509159,182,53692,642,042
1


This table actually includes data for all 50 states but it is too large to be posted on the board in its entirety. I'm basically looking to create a series of add-check edits within the table, but not exactly sure of the easiest way to set this up. There are three edits I want to set up:

1) the sum of federal, state, and local revenues in columns d, e, and f should be within 1 of the total revenues in column c. If not within 1, I wanted the total in column c to be shaded red

2) the sum of current spending, capital outlay, and other expenditures in columns h, i, and j should be within 1 of the total expenditures in column g.
If not within 1, I wanted the total in column g to be shaded red.

3) the sum of data for all the states from rows 11-70 (all rows are obviously not shown in the partial table I have above due to size restrictions on this board) should be within 1 of the u.s. total in row 9. Each column here needs to be checked individually. If not, I wanted the u.s. total in row 9 to be shaded red.

Is it possible to set this up without going through and typing in conditional formatting code for each individual cell? There are about 15 other tables in addition to this one where I need to do something very similar (though not exactly the same) so was looking to see if I could somehow copy and paste the conditional fomatting formulas to other cells without changing the actual values or border formats of the other cells? Is there an easier way to do this without using conditional formatting for cells?

Any advice is appreciated, thanks!!!

Julie
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

wwbwb

Well-known Member
Joined
Oct 20, 2003
Messages
513
for #1, highlight c11:c70 and apply a conditional formating of
Code:
=$c11<$d11+$e11+$f11-1
=$c11>$d11+$e11+$f11+1

for #2, hightlight g11:g70 and apply a conditional formating of
Code:
=$g11<$h11+$i11+$j11-1
=$g11>$h11+$i11+$j11+1

for #3, highlight c9 and apply a conditional formatting of
Code:
=c9<sum(c11:c70)-1
=c9>sum(c11:c70)+1
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,133
Messages
5,835,594
Members
430,369
Latest member
pingel

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