The user inputs data in Column A. Column J of the same row parses out
part of
the data (as part of a conditional formula located in Column J's
cells). Column A
has the conditional format itself, which interprets the data in the
corresponding
row of Column J. Depending on whether a match has been found (any
duplicate values of what in column J) the text on the current row in
Column A is formatted
to a certain color.

After hacking at this in VBA for a while, I did get it to work
(almost!). The only problem is when there is a duplicate entry, the
text formatting works, but it gives me
an error. Here is the code I wrote:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub

If Not Application.Intersect(Range("A1:A3000"), Target) Is Nothing
Then

Sheets("A").Activate
Dim i As Integer
Dim RowCount As Integer
Dim CellVaLue

Application.ScreenUpdating = False

'get the number of rows from the first column for the loop (column E)

Range("J65535").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
RowCount = Selection.Rows.Count

'define a starting place
Range("J1").Select

Target.Resize(1, 1).Font.ColorIndex = 1
Target.Resize(1, 1).Font.strikethrough = False

'Begining of Loop
'Loop through each of the rows of the spreadsheet
For i = 1 To RowCount

'IF I REMOVE THE '10' FROM THE LINE, THE ERROR
'GOES AWAY, BUT THEN IT DOESN'T WORK
CellVaLue = Cells(i + 1, 10).Value

'THIS IS THE LINE THE DEBUGGER HIGHLIGHTS
If (CellVaLue = (Target.Offset(0, 9).Value)) Then
Target.Resize(1, 1).Font.ColorIndex = 7
Target.Resize(1, 1).Font.strikethrough = True
End If

Next

If ((Target.Offset(0, 5).Value = "1") And (Target.Offset(0,
6).Value < "Yes")) Then
Target.Resize(1, 1).Font.ColorIndex = 3
Target.Resize(1, 1).Font.strikethrough = False
End If
End If
End Sub