Conditional formatting if entry is final entry of the day

Question

I have a Google Sheet in which I keep track of transactions, kinda like this:

| Date    | Funds   | Running total |
|---------|---------|---------------|
| 5/23/18 | 10.00   | 10.00         | 
| 5/23/18 | 18.00   | 28.00         | 
| 5/23/18 | (2.00)  | 26.00         | 
| 5/24/18 | 18.00   | 44.00         | 
| 5/25/18 | (50.00) | (6.00)        | 
| 5/25/18 | 84.00   | 78.00         | 

---

I'd like a way to make the Running total column be bold/highlighted (different), if it is the final entry of the day:

I've never actually done any Sheets scripting, and couldn't seem to figure out the formula, I tried

=(IF(A3<A4))

If the lower box is (a) greater (date) than the previous box, apply X formatting...

Well,

1. It doesn't work
2. I couldn't seem to figure out a way to apply the conditional formatting to the entire column (for each cell, I seemed to need to apply it manually).

---

I'm also looking for it to recognize when the final entry of a week is, but not sure if it's even possible. (Maybe this is too unrelated and I should post a new question.)

Answer 1

WELZ, you can do this with pure conditional formatting, no helper column.

Let's say your three example columns reside in A:C. Do this:

1. Select cell C1.

2. Choose "Format" from the menu, then "Conditional Formatting" and finally "Add new rule."

3. The box that says "Apply to range" will say "C1." Replace this with "C:C" instead.

4. Under "Format cells if..." click the scroll arrow, scroll all the way to the bottom of the drop-down menu, and select "Custom formula is."

5. In the box below that (where it says "Value or Formula"), enter this formula:

   =AND(ISNUMBER(A1),OR(A2>A1,A2=""))

   The last entry of each day will be highlighted (mint green by default for now).

6. Use the "Formatting Style" options to style as you see fit.

7. When finished, click the blue "Done" button.

---

(Added 10/12/18 per request)

WHY/HOW the CF formula works: =AND(ISNUMBER(A1),OR(A2>A1,A2=""))

=AND( ___________ , ___________ )

Two conditions MUST be met.

=AND(ISNUMBER(A1), ___________ )

If you've followed the above instructions, you will have changed "C1" in the Conditional Formatting dialog box to "C:C"; this means that whatever happens to Row 1 of that range will happen to every row. So when we apply ISNUMBER() to cell A1, it will actually check A1 relative to C1, A2, relative to C2, etc.

ISNUMBER(A1) is the first condition of the AND() that must be met. It will essentially check to see that you have a date (which Google Sheets sees as a formatted number) in Column A. In English, Google checks this: "Only apply any conditional formatting to the cell in Column C if the cell in Column A of the same row contains a date (i.e., don't apply any formatting if there is text, such as a header, in Column A of that row, or if Column A for that row is blank)."

=AND(ISNUMBER(A1),OR( ______ , ______ ))

The second condition of the AND() that must be met is that at least one of two things must also be true.

=AND(ISNUMBER(A1),OR(A2>A1, ______ ))

Either the cell below (i.e., A2) the cell in Column A of this row (i.e., A1) must be a higher date (marking the current row as the last entry of the date in Column A of that row), OR...

=AND(ISNUMBER(A1),OR(A2>A1,A2=""))

...that next cell in Column A (below the current row, i.e., A2) must be blank (which will catch the date in the final filled row as the last entry of that day, since there are no more dates to measure it against after that, just blank rows).

---

All together, in English: "Format the cell in each row of Column C if the cell in Column A of that row contains a date AND if either the cell below that date is higher or blank."

Answer 2

Create a new junk column that you can then hide:

=IF(A1<A2, "Y", "N")

Then do a custom conditional formatting that:

=FIND("Y",C1)

Like so: