Macro with a loop (any)

Topic

New Reply

I have large worksheet with columns: date, author, title, etc, sorted ascendingly. My customer wants date shows only once, then author shows only once, etc. The problem is that worksheet is too large (several hundred rows) to do it manually. I need to write a macro, that compares cell A2 with A1, and if values are equal, changes text color to white (color index 2), then goes to cell A3, compares with A2, etc. Then at the end of column A (both cells are empty) goes to column B, and continues to do this to the end of worksheet (column is empty). Can anybody help me?

Reply | Quote

Replies

I think Conditonal formatting is a better bet, as it is more dynamic. If a cell value changes there would be no need to worry about updating the color etc. In A2 apply th efollowinh conditional formatting :

If Cell Value Is 	 equal to 	 = A1	(see attached picture) 

then copy that and paste special formats to all cells except those in row 1.

Andrew C

Reply | Quote

Thank you, but you miss the point. I know how to use Conditional Formatting, but I don’t know how to run it through all worksheet. “Copy and paste” means at least couple days of work, and my worksheet is static, next month will be another one.
I need AUTOMATE this process.

Reply | Quote

Here’s a macro for you:

Option Explicit
Sub ClearA1Dups()
Dim strOrig As String
Dim intOrig As Integer
Dim c As Range
    With Range("A1")
        strOrig = .NumberFormat
'       intOrig = .Font.ColorIndex
        For Each c In ActiveSheet.UsedRange
            If c.Value = .Value Then
                c.NumberFormat = ";;"   ' or
'               c.Font.ColorIndex = 2
            End If
        Next c
        .NumberFormat = strOrig
'       .Font.ColorIndex = intOrig
    End With
End Sub

I used the ;; custom number format to make the date invisible because I like it better, but if you want to do it with white numbers, then use the code which is commented-out instead. This only does the current sheet, we can easily add a loop for each worksheet, if you want. HTH –Sam

Reply | Quote

I think conditional formatting is best, as if any thing changes that should be taken care of automatically. If you do as I suggested all you have to do is copy A2, and then select the cells you wish to treat and Paste Special, Formats. As quick as, and probably much quicker in a large range, than evaluating each cell and formatting via VBA code.

Here is some code to apply the conditional formatting to every cell on the sheet, except row 1. If youdo not want to include all cess, use UsedRange in place of Cells.

Sub WhiteDupes()
Dim strCond As String
Dim oCell As Range
    For Each oCell In Cells
        If oCell.Row > 1 Then
             strCond = Chr(34) & "=" & _
                ActiveCell.Offset(0, -1).Address & Chr(34)
             Selection.FormatConditions.Delete
             Selection.FormatConditions.Add _
                Type:=xlCellValue, Operator:=xlNotEqual, _
                Formula1:=strCond
            Selection.FormatConditions(1).Font.ColorIndex = 2
        End If
    Next
End Sub

I would still suggest using the conditional formating togetherr with paste formats.

Andrew C

Reply | Quote

I agree, Andrew, that conditional formatting is best for all of the reasons that you stated, plus you can avoid using VBA. I just wrote the macro because Kaplin wanted it. –Sam

Reply | Quote