excel formatting

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
jamesmetcalf74Asked:
Who is Participating?
 
Rob HensonFinance AnalystCommented:
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
 
jamesmetcalf74Author Commented:
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
 
Rob HensonFinance AnalystCommented:
Simple answer is no, just hide it instead.
0
 
jamesmetcalf74Author Commented:
Thanks Rob-
This will make working with the data much easier
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Nice solution Rob! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.