• Macro with a loop (any)

    Author
    Topic
    #363581

    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?

    Viewing 0 reply threads
    Author
    Replies
    • #555235

      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

      • #555265

        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.

        • #555280

          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

        • #555287

          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

          • #555679

            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

    Viewing 0 reply threads
    Reply To: Macro with a loop (any)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: