VBA equivalent of match
 

Hello all,

I am currently employing a conditional format to a column
of cells, whose text colors are contingent on a set of conditions

=IF((J98<"")*(J98<" ")*(ISNA(MATCH(J98,$J$1:J97,0))),FALSE,TRUE)
'Magenta strikethrough text

=IF(AND(F98=1,G98<"Yes"),TRUE,FALSE)
'Red Text

=IF(OR(A98="Table 7",A98="table 7",A98="Table7",A98="table7"),
TRUE,FALSE)
'Regular, black text

Unfortunately, if any user selects a cell they want to edit in this
column (this is supposed to happen) without double-clicking on it
(hence the cell is active, but the cursor is not) & pastes data in - it
removes all of the conditional formatting for that cell.

I figured that the most sensible way around this is to write VBA
code that would handle this, but I'm not sure how to translate the
above conditions into VBA. Any help on this would be much appreciated.

Thanks.

VBA equivalent of match
 

Where are you adding the CF, which cells?

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
Hello all,

I am currently employing a conditional format to a column
of cells, whose text colors are contingent on a set of conditions

=IF((J98<"")*(J98<" ")*(ISNA(MATCH(J98,$J$1:J97,0))),FALSE,TRUE)
'Magenta strikethrough text

=IF(AND(F98=1,G98<"Yes"),TRUE,FALSE)
'Red Text

=IF(OR(A98="Table 7",A98="table 7",A98="Table7",A98="table7"),
TRUE,FALSE)
'Regular, black text

Unfortunately, if any user selects a cell they want to edit in this
column (this is supposed to happen) without double-clicking on it
(hence the cell is active, but the cursor is not) & pastes data in - it
removes all of the conditional formatting for that cell.

I figured that the most sensible way around this is to write VBA
code that would handle this, but I'm not sure how to translate the
above conditions into VBA. Any help on this would be much appreciated.

Thanks.

VBA equivalent of match
 

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

VBA equivalent of match
 

see your other post for a suggestion regarding your type mismatch error.

" wrote:

Hello all,

I am currently employing a conditional format to a column
of cells, whose text colors are contingent on a set of conditions

=IF((J98<"")*(J98<" ")*(ISNA(MATCH(J98,$J$1:J97,0))),FALSE,TRUE)
'Magenta strikethrough text

=IF(AND(F98=1,G98<"Yes"),TRUE,FALSE)
'Red Text

=IF(OR(A98="Table 7",A98="table 7",A98="Table7",A98="table7"),
TRUE,FALSE)
'Regular, black text

Unfortunately, if any user selects a cell they want to edit in this
column (this is supposed to happen) without double-clicking on it
(hence the cell is active, but the cursor is not) & pastes data in - it
removes all of the conditional formatting for that cell.

I figured that the most sensible way around this is to write VBA
code that would handle this, but I'm not sure how to translate the
above conditions into VBA. Any help on this would be much appreciated.

Thanks.