Validate cell value against a list

Question

Hi,

I am trying to work out how I can write a piece of code to look at cells in column R and tell me whether the value in them is contained within the range on a sheet named "RC" cell range A1:A13.  If it is not contained in this range then I want the cell to be coloured yellow.

I got a kind response from someone earlier regarding how to identify some other cells I needed highlighting but I cannot fathom how to compare it against a range.  The code I am using looks something like below but the range part I have tried to add in is not working.

Sub RCodes()

Set sht = Sheets("RawData")

Dim myrange As Range, c As Range, reason As Range

Dim LastRow As Long

LastRow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row

Set myrange = sht.Range("r2:r" & LastRow)

Set reason = Sheets("RC").Range("A3:A12")

For Each c In myrange

If Not c.Value = reason.Value = True Then

    c.Interior.ColorIndex = 6

End If

Next

End Sub

Thanks for your help!

Answer 1

Whoops.  Good catch.

Sorry about that.

Answer 2

Dim myrange, c, r, reason As Range

For the above line from your code... while the names seem to indicate you want all four variables to be declared as Range, actually only the last one ("reason") gets declared as Range... the other three are getting declared as Variant (the default when no type is specified). Unlike many other languages, VB requires the Type declaration be specified on each variable; otherwise those variable get declared as the default variable type of Variant. Your code line should be following in order to do what I think you wanted as a result...

Dim myrange As Range, c As Range, r As Range, reason As Range

Answer 3

Set reason = Sheets("RC").Range("A3:A12")

For Each c In myrange

If Not c.Value = reason.Value = True Then

You can not compare one value with a couple of values, reason.value return's an array.

Normaly you have to compare c with each value in reason, but here you can use FIND to have a "quick look" if the value is there.

Andreas.

Sub RCodes()

  Dim MyRange As Range, Reason As Range

  Dim C As Range, R As Range

  With Sheets("RawData")

    Set MyRange = .Range(.Range("R2"), _

      .Range("R" & Rows.Count).End(xlUp))

  End With

  Set Reason = Sheets("RC").Range("A3:A12")

  For Each C In MyRange

    Set R = Reason.Find(C.Value, LookIn:=xlValues, _

      LookAt:=xlWhole)

    If R Is Nothing Then

      C.Interior.ColorIndex = 6

    End If

  Next

End Sub

Answer 4

A few things.

"If Not c.Value = reason.Value = True Then" should just be "If Not c.Value = reason.Value Then"

You're not checking the value of each cell in the range reason, you're just checking the value of the whole range.

Add/Change:

Dim myrange, c, r, reason As Range

For Each c in myrange

     For Each r in reason

           If Not c.Value = r.Value Then

                   c.Interior.ColorIndex = 6

           End If

     Next

Next

Answer 5

Look in the vba help index for FIND. Then try to find each value in the source range. For more than one possible occurence of each search item use FINDNEXT instead.

---

Don Guillett MVP Excel SalesAid Software *** Email address is removed for privacy ***