2 Line Entry Checkbook - Highlight Consecutive Rows Based on Text

**nesbensen** · 10-12-2012, 07:10 PM

I have a checkbook register with dual line entries...the top line has most of the info and the second line underneath has the detailed description about the entry. I can get the first line to highlight in a specific color based on the first column text entry, but I need the second line underneath to also highlight in that same color based on the text in the first column of line one. How can I get both line 1 and line 2 of the entry to highlight based on the text in column A of the first line? Example in the below entry: Highlight the 2 lines of the all Food entries in purple; highlight the 2 lines of all Payment entries in green; highlight both lines of all Deposit entries in yellow, etc. where "Food" is the text in Column A to base the color highlight on; "Payment" is a different Column A entry and so on. Also the second line in Column A has no text and is a blank cell.

I tried to have an example but couldn't get the 2 lines to line up and it didn't look like it should. Thanks for any help!

**FDibbins** · 10-12-2012, 07:33 PM

when you go into CF, select "use formula" you can then use the same criteria for the 2nd row as you did for the 1st row.

if you still have a problem, upload a sample and i will walk you thru it

**nesbensen** · 10-13-2012, 12:10 PM

Thanks for your quick reply! I've attached one page of sample register. I guess I am not totally understanding the values and how they are used in the formulas. Thanks again for your help!

**FDibbins** · 10-13-2012, 03:26 PM

change the formulas in your CF to =OR($A1="Purchase",$A2="Purchase") etc this should give you what you want

**nesbensen** · 10-13-2012, 05:57 PM

Nope...doesn't work because there is no text in the second line that i am basing the formula on in the first line. For example, cell A4 text (Purchase) that is used to highlight line 4 is NOT in cell A5, which is blank. There is no text is A5, but I want line 5 to highlight same color as line 4 based on the text in A4. Both lines 4 AND 5 should be highlighted the same color based on the text in cell A4. The lines are white in the attached example when they should be the same color as the line above it.

Thanks again for your help! I appreciate it!

**FDibbins** · 10-13-2012, 07:12 PM

It worked the 1st time i tested it, and it woked again this time too. maybe you entered the formula incorrectly?

i have made the change for "Purchases" and "Gas", you will need to change the others yourself

see the attached

**nesbensen** · 10-14-2012, 11:08 AM

IT WORKS!!! IT WORKS!!!

Thank you so much for your help! I entered the WRONG cell number when I tried the formula...and then just copied the same wrong cell reference when trying it for other cells which is why it didn't work on my end. So I apologize for making extra work for you!

But now it's works just perfectly so I can't thank you enough for your time.

One thing is confusing me, though. Since line 1 is the heading that repeats at the top of every page, how come the formula works referencing cells A1 and A2? The actual line items are lines 2 and 3.

And a second thing I am not following is since there is no text in the second line, first cell (A5, for example), and the formula references that blank cell to base the formula, how does it work? In other words, the formula is saying if the text in cell A5 says "gas" then highlight blue. But that cell A5 is blank and never will say "gas". So how does it work?

Thanks again for all of your time and help...and patience!!! I really do appreciate it. I have been trying to figure this out for months and have just highlighted the lines manually. You have made my day...now if my Eagles win today too, I just won't be able to contain myself!!!