Color code in 2 column conditional

[Charles L. Snyder]

Hi

I have a spreadsheet similar to this:

RJS JPM CLS CALL BU
VAC MTG CLS
MTG VAC VAC JPM
MTG VAC JPM


My code looks like this:

Sub ck_conflicts()

Dim rng As Range, cell As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "K").End(xlUp).Row
Set rng = Range("K4:K" & lastrow)
For Each cell In rng 'offset is 7 columns
If (cell.Value = "RJS" And cell.Offset(0, -7).Value <> "") Then
cell.Interior.ColorIndex = 3
MsgBox ("Conflict Found - Color Coded")
Else
MsgBox ("No Conflicts Found")
Exit Sub
End If
Next
End Sub


I am trying to look down each cell in the "Call" column (= K), and
identify where someone's initials are. I then want to look over to the
column where their initials are (same row) and see if the cell is empty
(ie, no conflicts). If there is a conflict, then color the cell. In the
example spreadsheet above, there is a conflict where JPM is listed in
the call column, but also listed as vacation (VAC). I can't see what is
wrong with my code snippet....


Thanks

CLS

 

[Tom Ogilvy]

Sub ck_conflicts()

Dim rng As Range, cell As Range
Dim lastrow As Long, bConflict as Boolean
lastrow = Cells(Rows.Count, "K").End(xlUp).Row
Set rng = Range("K4:K" & lastrow)
For Each cell In rng 'offset is 7 columns
If (cell.Value = "RJS" And cell.Offset(0, -7).Value <> "") Then
cell.Interior.ColorIndex = 3
bConflict = True
End If
Next
if bConflict then
msgbox "One or more conflict was found"
Else
msgbox "No conflicts"
End if
End Sub