Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Sep 2005
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts
    UNIT1 UNIT2 UNIT3 UNIT4 UNIT5 UNIT6 RESULT

    59945 59945 59945 72641 59945 59945



    I apologize if this is found already somewhere in a thread. I am also not used to doing things in excel - forgive me if my questions are simple.
    I have the following columns in excel. Someone will be using a scanner gun to enter information into each field - across the columns - UNIT1, then UNIT2, etc. After UNIT6 is entered i need to somehow compare the 6 numbers entered and if they are all the same the result field should say "yes" - if they are not all the same the result field should say "NO" and be red. Is this possible to do in VBA and if yes, how do i do that? The amount of rows entered could be only a few or 1000's. If there is some other program that someone can suggest to do this in that would be easier i am open to ideas.

    Hope this makes sense. Thanks in advance for your help.
    Lu

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can do this with a formula and conditional formatting.
    In G2
    Code:
    =IF(COUNTA(A2:F2)=6,IF(COUNTIF(A2:F2,A2)=6,"Yes","No"),"")
    For the conditional formatting, with G2 selected, assign this formula to the conditional formatting
    Code:
    =G2="No"
    After you've enter the formula and the conditional formatting, copy G2 down to the end of your data.

    I've attached a sample.
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Sep 2005
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you - that works great.

    I am wondering though - is there a way to do that in VBA code behind the scenes instead of just a formula in the spreadsheet?
    Part of my concern is someone messing with the formula. Second, if i only copy the formula down to row 10 and someone uses row 11 and beyond then i have to keep going back in and adjusting the formula down further.

    Thoughts??

    Thanks

  4. #4
    Lounger
    Join Date
    Sep 2005
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Also - is it possible that once the yes or no is posted an 'enter' is automatically applied so that the cursor then goes to the next row?

    Thanks

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Use this code assigned to the the Worksheet_Change event:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns(6)) Is Nothing Then
        If Application.WorksheetFunction.CountIf(Target.Offset(0, -5).Resize(1, 6), Target) <> 6 Then
            Application.EnableEvents = False
            With Target.Offset(0, 1)
                .Value = "No"
                .Font.Color = vbRed
            Application.EnableEvents = True
            End With
        Else
            Application.EnableEvents = False
            With Target.Offset(0, 1)
                .Value = "Yes"
                .Font.Color = vbNormal
            Application.EnableEvents = True
            End With
    
        End If
    Target.Offset(1, -5).Select
    End If
    
    End Sub
    Attached Files Attached Files

  6. #6
    Lounger
    Join Date
    Sep 2005
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you!!!

Posting Permissions

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