Can't find my favorite code. And, can't seem to find it in a search here either

A VBA loop for a range.

Sorted data lets say in Column A
Bold the cell when the data changes, lighten the color of the cells inbetween.

Example:

10
10
10
20
20
30
40
40
40
40
50

Something like

For Each c In ActiveWindow.Selection
If c.Value <> c.Offset(-1, 0).Value Then
c.Font.FontStyle = "Bold"
End If
Next c

Brian

Thanks! :)
That is the old code I was looking for
to no avail after searching on cell, offset and other key words for an hour.
Just too many key words.
My old "Microsoft Excel 97 Developer's Handbook" by Eric Wells andn Steve Harshbarger has an example on page 113, but the code is on my long lost CD ROM.
We can only dream of a book of this quality for Excel 2010

Perhaps I should write a book. ;)

Just kidding

Brian

Thanks so much again! :D Finished coding a very complex report.
Hope someone else might enjoy the code (and fragements)
When creating a recordset object, the number of records go into a variable. This is useful to format exactly the right number of rows, rather than an entire column.
This code sets an object reference to Excel, creates a new worksheet and runs the code from Access.
After the report is finished, the selection in column B starts at row 6 and runs the loop and then stops at the last row of the recordset size copied into Excel for that time.



Dim objXL As excel.Application
Dim XLWB As excel.Workbook
Dim XLWS As excel.Worksheet
Dim strSQL As String
Dim rsData As DAO.Recordset
Dim intRowPos As integer ' first data row used for a page full of code
Dim intMaxRecordCount As integer ' each record set - know where it ends
Dim rngRange As excel.Range
Dim c As excel.Range

intRowPosition = 6 ' start .copyfromrecordset at row 6 (not shown)
objXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).copyfromrecordset rsData ' rsData holds data example
intMaxRecordCount = rsData.RecordCount - 1

objXL.Worksheets(intWorksheetNum).Cells(intRowPos - 1, intHeaderColCount + 1) = rsData.Fields(intHeaderColCount).Name ' example of variable usage


objXL.Range("B" & intRowPos & ":B" & intMaxRecordCount + (intRowPos)).Select
' Row B has Customer Names in order with dates in order

For Each c In objXL.ActiveWindow.Selection
If c.Value <> c.Offset(-1, 0).Value Then
c.Font.FontStyle = "Bold"
End If
Next c
Set c = Nothing

Perhaps I should write a book. ;)

Just kidding

Brian

Let me know when it is published Brian :)