VBA match cells Help

RickeyS

Board Regular
Joined
Jun 23, 2006
Messages
222
Hi, I need to vba code for the following:

I have a spreadsheet with names in cells C16:I43 what I need to do is if a the same name appears in row C & D highlight those cells with the same name and then do this for rows D & E, then E & F, then F & G and so on.

The way my spreadsheet is layed out is row C & D is day one row E & F is day two row G & H is day three and so on, then row C would be day work and row D night, row E day work and row F night work and so on.

What I'm trying to do is make sure that the same person is not scheduled for day work on day one and then scheduled for night work on day one, or for night work on day one and day work on day two, etc.

Hope this makes sense.
Thanks
Rick
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi Rick

Maybe I did not understand well but I don't think that you need vba. It seems to be just a conditional format.

From what I understood the condition is:
Highlight this cell if the name in this cell appears also either in the row before or in the row after.

Hope this helps
PGC
 
Upvote 0
The problem is that I'm already using the conditional formats for other things on this sheet.

Rick
 
Upvote 0
try;
Code:
Sub test()
Dim x, y, z As Long
x = 3
y = 43
z = 4
For ii = x To 7
For i = 16 To y
    With Range(Cells(16, z), Cells(43, z))
        Set c = .Find(Cells(i, ii).Value, , , xlWhole)
            If Not c Is Nothing Then
            If c.Value <> "" And Cells(i, ii) <> "" Then
            c.Interior.ColorIndex = 6
            Cells(i, ii).Interior.ColorIndex = 6
            End If
            End If
     End With
Next
z = z + 1
x = x + 1
Next
End Sub
 
Upvote 0
Hi RickeyS:

In addition to contribution from agijcam, you may also want to try ...
Code:
Sub y_1()
    For Each cell In Range("C16:C43")
        If cell = cell.Offset(1, 0) Then
            Range(cell.Offset(0, 0), cell.Offset(0, 6)).Interior.ColorIndex = 36
        End If
    Next cell
End Sub
 
Upvote 0
Hi again Rick

As I understood the name can be in the lines before or after but not necessarily on the same column.

Another option:

Best regards
PGC

Code:
Sub NoDoubleShift()
Dim rCell As Range, rRng As Range, rRow As Range, rFound As Range

Set rRng = Range("C16:I43")
For Each rRow In rRng.Rows.Resize(rRng.Rows.Count - 1)
    For Each rCell In rRow.Cells
        Set rFound = rRow.Offset(1).Find(what:=rCell.Value, _
               LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        If Not rFound Is Nothing Then
            If rCell <> "" Then
              rCell.Interior.ColorIndex = 4
              rFound.Interior.ColorIndex = 4
            End If
        End If
    Next
Next
End Sub
 
Upvote 0
None of the above seem to do anything, as you can see from my spreadsheet Shelson is working Mon-18 Day shift (D) and Tue-19 Day Shift (D). If I was to mistakenly put his name in for Mon-18 night shift (N) I want the cells to turn some color to show error, again I can't use conditional formating because I already use it for other things on spreadsheet for these cells. And I need this solution to work for any name on spreadsheet, I want to avoid any person from working two shifts one directly after the other without time off in between.

Thanks
Rick
Work.xls
ABCDEFGHI
15DateshiftHOCCRLoHoCatFlexPrimary
16Mon-18DShelsonDavidCooperGlenkowski
17C/DNDeritisScottFarrell
18Tue-19DShelsonDavidCooperGlenkowski
19C/DNDeritisScottFarrell
20Wed-20DDonovanNeckonchukFinneganKotykAntonucci
21A/BNAtkinsWhiteReillyFordHenry
22Thu-21DDonovanNeckonchukFinneganKotykAntonucci
23A/BNAtkinsWhiteReillyFordHenry
Week3&4
 
Upvote 0
Hi again

I just loaded your data into a worksheet changed some workers' shifts to have 2 shifts one directly after the other and run my code. It worked. Did you test it?

Another thing. In your first post you wrote "if a the same name appears in row C & D highlight those cells". Now, looking at you example It seems that it is simply "if a the same name appears in row C & D IN THE SAME COLUMN highlight those cells". If this is so the problem gets much simpler and you can simplify the code, like:

Code:
Sub NoDoubleShift()
Dim rCell As Range, rRng As Range

Set rRng = Range("C16:I43")
For Each rCell In rRng
    If rCell = rCell.Offset(1) And rCell <> "" Then _
        rCell.Resize(2).Interior.ColorIndex = 4
Next
End Sub

If when you have the double shift the names appear always in consecutive rows in the same column, use this code instead. If the names appear in consecutive rows but not necessarily in the same column use the code on my previous post.

Hope this helps
PGC
 
Upvote 0
Hi
Code:
Sub test()
Dim i As Long, ii As Long, n As Integer
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 3 To 6
        For ii = 16 To Range("a" & Rows.Count).End(xlUp).Row
            If Not IsEmpty(Cells(ii,i)) And Not .exists(Cells(ii,i).Value) Then _
                .add Cells(ii,i).Value, ii
        Next
        n = 2
        For ii = 16 To Range("a" & Rows.Count).End(xlUp).Row
            If .exists(Cells(ii,i+1).Value) Then
                n = n + 1
                Cells(ii,i + 1).Interior.ColorIndex = n
                Cells(.item(Cells(ii,i+1).Value),i).Interior.ColorIndex = n
            End If
        Next
        .removeall
    Next
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top