Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

excel formatting

Posted on 2016-09-13
5
Medium Priority
?
49 Views
Last Modified: 2016-09-13
I have an excel spreadsheet that has thousands of billing line items.
Column A lists the accountnumber and it is ordered by accountnumber.... low to high

so for example..... account 1111 might have 3 rows.  one row for each billing line item... ie... phone bill, garbage bill, sewer bill
account 2222 might only have 2 rows...   electric bill, garbage bill

i'd like to color the rows with a ledger style row color... by account.
meaning   account 1111... its 3 rows would be light green
account 2222's 2 rows would be white
account 3333's rows would revert back to light green.

is there a way to do this.  I really don't care what colors they are... just as long as they are light and transparent
attached is a manually created file that looks like what I am talking about.
experts-help.xlsx
0
Comment
Question by:jamesmetcalf74
  • 2
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 41796227
You can do that with Conditional formatting.

Insert a column to the left of the data, making this column A.

In A2 type 1 and then in the rest of column A have the following formula:

=IF(B3=B2,A2,-A2)

Copied down, this will alternate between 1 and -1 for each account.

Then for the conditional formatting:

Select all the data, excluding headers
Click on Home Ribbon > Conditional Fomatting and Choose New Rule
Select Use a formula to determine which cells to format
Enter formula =$A2=1   (note use of $ for column A, if you have used a different column adjust that)
Format as required for one colour eg pale green fill
Click OK

Assuming the cells are currently just white/no fill, only those with 1 in column A will now be pale green.
1
 

Author Comment

by:jamesmetcalf74
ID: 41796262
that worked like a charm but....   when i delete column a (the 1 and -1 column)  the formatting disappears.

which makes sense since it is formatting off that data.
is there a way to make the formatting stick and delete the column i am using.
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 41796267
Simple answer is no, just hide it instead.
0
 

Author Closing Comment

by:jamesmetcalf74
ID: 41796273
Thanks Rob-
This will make working with the data much easier
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41796293
Nice solution Rob! :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question