Mark all cells which do not include two given words

Question

I am writing a code which should mark in a range, in red, all cells which do not include the words "bel" and "hsa".

Sub Check()
    Range("c2:c49").Select

    For Each cell In Selection
        If (cell.Value <> "bel" Or cell.Value <> "hsa") Then
            cell.Interior.Color = RGB(255, 0, 0)
        End If
    Next cell
End Sub

It works if I only have one argument. With the two arguments like above, it marks all cells in range red.

Is the Or operator wrong?

Unless you're doing this as an exercise to practise VBA, I suggest using conditional formatting instead. — jsheeran, Mar 13 '19 at 08:22

Pᴇʜ · Answer 1 · 2019-03-13T08:32:27.513

Note that this task can be solved with Conditional Formatting which has the advantage that it updates automatically if data changes and you don't need to run the procedure over and over again.

The following is just to improve your coding:

Avoid using Select in Excel VBA. • Instead use the range directly and also specify in which worksheet the range is. If you don't do that Excel guesses which sheet you meant to use and it might fail:
```
For Each cell In Worksheets("MySheet").Range("C2:C49")
```
I recommend always to activate Option Explicit and declare all your variables. This is a very good practice and prevents many errors: In the VBA editor go to Tools › Options › Require Variable Declaration.
Your actual issue is that if you want to exclude both "bel" and "hsa" then (of course) you need to use the And operator.
```
Cell.Value <> "bel" And Cell.Value <> "hsa"
```
Note that <> versus = always changes the logic of the operators or/and. Alternatively you could use:
```
Not (Cell.Value = "bel" Or Cell.Value = "hsa")
```
… which would give you the same result. Use the one that is easier to understand for you.

So you end up with something like:

Option Explicit

Public Sub ValidateAndColorCells()
    Dim CheckRange As Range
    Set CheckRange = Worksheets("YourSheet").Range("C2:C49") 'Always specify the sheet!

    Dim Cell As Range '<-- declare ALL your variables
    For Each Cell In CheckRange 
        If Cell.Value <> "bel" And Cell.Value <> "hsa" Then
            Cell.Interior.Color = RGB(255, 0, 0)
        End If
    Next Cell 
End Sub

Mark all cells which do not include two given words

1 Answers1