I maintain a time log of my activity throughout the day.
I have a short little bit of code (below) that adds five new rows to the bottom of my table with today's date in the first column. It does that, but for the last row, the existing Conditional Formatting rules don't apply correctly.
=ISEVEN($B5) (applies to entire table)
=ISODD($B5) (applies to entire table)
So the visual end result is color banding by alternating dates (the values in Column B) rather than alternating rows.
Solutions would be greatly appreciated -- I've found a lot of near misses, but nothing directly on target. Something that just forces the Conditional Formatting rules to run? Something that pastes formatting from the first row to the added row?
I have a short little bit of code (below) that adds five new rows to the bottom of my table with today's date in the first column. It does that, but for the last row, the existing Conditional Formatting rules don't apply correctly.
- If I manually type the date over, the Conditional Formatting does not apply itself.
- If I delete the date, then type the date, the Conditional Formatting does not apply itself.
- If I open Conditional Formatting > Manage Rules and hit OK, the Conditional Formatting does not apply itself. The Applies to range is listed correctly.
- If I open Conditional Formatting > Manage Rules and select the Applies to range for either of my rules -- and do nothing else -- and hit OK, the Conditional Formatting applies correctly.
- If I run the code twice, when it runs the second time, the Conditional Formatting applies correctly to the previous "last line" but does not apply correctly to what is now the last line. No matter how many times the code runs, only the last line's Conditional Formatting is incorrect.
=ISEVEN($B5) (applies to entire table)
=ISODD($B5) (applies to entire table)
So the visual end result is color banding by alternating dates (the values in Column B) rather than alternating rows.
Solutions would be greatly appreciated -- I've found a lot of near misses, but nothing directly on target. Something that just forces the Conditional Formatting rules to run? Something that pastes formatting from the first row to the added row?
VBA Code:
Sub Add_Five_Rows()
Dim count As Integer
Dim daterow As ListRow
For count = 1 To 5
Set daterow = wsLog.ListObjects("TblLog").ListRows.Add
With daterow
.Range(1).Value = Date
End With
Next
End Sub