Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    2,328
    Thanks
    0
    Thanked 1 Time in 1 Post

    Macro with a loop (any)

    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?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro with a loop (any)

    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 :<pre>If Cell Value Is equal to = A1 (see attached picture) </pre>

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

    Andrew C
    Attached Images Attached Images

  3. #3
    Silver Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    2,328
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro with a loop (any)

    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.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro with a loop (any)

    Here's a macro for you:
    <pre>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</pre>

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro with a loop (any)

    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 <font color=red>Cells</font color=red>.<pre>Sub WhiteDupes()
    Dim strCond As String
    Dim oCell As Range
    For Each oCell In <font color=red>Cells</font color=red>
    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</pre>

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

    Andrew C

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro with a loop (any)

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •