Highlight Duplicate Rows Based On 1 Column

  • I need VBA help. I know how to highlight every other row but what I can't do is highlight a row if column values are the same. For example I have 5 columns but only the first one has the data I'm interested in. If the data in the first column looks something like this:


    1
    2
    3
    4
    4
    5
    5
    5
    6
    6
    6
    7
    .
    .


    I want to highlight every other row that has similar values therefore I want to highlight row 2, both row 4s, and three 6s...etc.


    I also want it to start on the 2nd row of every page until the last row of every page which is row 37 in my case.

  • Re: Highlight row(s) Based On Column Values


    What's your code ilke right now that highlights every other row? Probably just need to add a bit in that compares the next rows value to the previous to verify what the next "real" other row is.

  • Re: Highlight row(s) Based On Column Values


    Here's the Macro I was using


  • Re: Highlight row(s) Based On Column Values


    This will process the cells of the leftmost column of the current selection and highlight the entire rows:

    p45cal[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Oops, missed the last line of your post. Quick and dirty solution, run this which calls the previous macro:

    Code
    Sub blah5()
    For Each sht In ActiveWorkbook.Sheets
    If sht.Visible Then sht.Activate 'only processes visible sheets in a book
    Range("A2:A37").Select 'assumes column A is the column you're interested in
    blah4 'calls previous macro
    Next sht
    End Sub

    BTW, I'd change the line

    Code
    HighlightOn = True

    in blah 4, to:

    Code
    HighlightOn = False

    to stop a sheet with no information, or with all the same info in column A, being highlighted at all.
    p45cal

  • Re: Highlight Duplicate Rows Based On 1 Column


    My question was actually to Dave, because it wasn't immediately obvious to me how conditional formatting with a formula could work. Are you OK now, or do you still need some help?

  • Re: Highlight row(s) Based On Column Values


    Quote from Dave Hawley

    No need for code, use Conditional Formatting.

    Dave, could you show me how to do what the OP wanted using conditional formatting - no simple solution comes to mind, but then I'm no expert (I'm, sure Bub Umlas would have a 3 character CF formula!). I'm not trying to score points here, just to know of a good CF solution for my archives.
    p45cal

  • Re: Highlight Duplicate Rows Based On 1 Column


    At risk of being accused of stating the obvious: if the numbers are in sequence then =MOD(A2,2)=0 in conditional format generates the required highlight.


    Otherwise, the formula has to detect the format of the previous row and switch it if the value changes. And I can't see how that format can be detected.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!