Excel, Marking a field when text is not like required

[jmslab]

Hello,

I like to have a module which marks the datafield, in column A from
record 2 till last, in color red when the registered data is not equal
to "aaa" or "bbb" or "ccc".
Can you help me out.

Johan

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Sonic()
Dim R As Range
Dim V As Variant
Dim S As String
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
S = "aaa,bbb,ccc"
V = Split(S, ",")
For Each R In Range("A1:A" & lastrow)
If IsError(Application.Match(CStr(R.Value), V, 0)) Then
R.Interior.ColorIndex = 3
End If
Next R
End Sub

Mike

 

[Patrick Molloy]

you could use Conditional Format on A2.A999

use 'formula is' and

=NOT(OR(A8="aaa",A8="bbb",A8="ccc"))
set a pattern color as required

Patrick Molloy