Format cell value change in sorted column

[Rx_]

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

 

[Brianwarnock]

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

 

[Rx_]

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

 

[Brianwarnock]

Perhaps I should write a book.

Just kidding

Brian

 

[Rx_]

Thanks so much again! 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

 

[Fear Naught]

Perhaps I should write a book.

Just kidding

Brian

Let me know when it is published Brian