Code to colour in cell when criteria not met

[Dazzawm]

Hi, I need a code to look at the data in column AB and then look at column AC and colour in the cells if the criteria is not met.

Criteria:-

If AB has the data starting with A then the data in AC must also start with A, if the data in AB starts with either N or J then the data in AC must start with N and finally if the data in AB starts with V then the data in AC must start with S. So like I say if the criteria is not met then I would like the cells highlighted.

It has 35000 rows of data to look at so a code would be better than an If/And type formula. Thanks.

 

[mirabeau]

do you mean data in corresponding rows (with same row index) or do you mean in any rows?

 

[Dazzawm]

Yes in the corresponding row please. i.e AB36 and AC36 etc..

 

[Dazzawm]

^

 

[mirabeau]

Try this and see if it's your sort of thing.

Whether or not, I'm afraid I'm out for the rest of the night.

Sub hilights()
Dim lrab As Long, lrac As Long
Dim mx As Long, i As Long
Dim ab, ac, x, y
Dim ash As Worksheet
Set ash = ActiveSheet

lrab = ash.Cells(Rows.Count, "ab").End(xlUp).Row
lrac = ash.Cells(Rows.Count, "ac").End(xlUp).Row
mx = Application.Max(lrab, lrac)
ab = Range("AB1").Resize(mx)
ac = Range("AC1").Resize(mx)

For i = 1 To mx
x = Left(ab(i, 1), 1)
y = Left(ac(i, 1), 1)
flg = 0
If ((x = "A") * (y = "A")) _
    + (((x = "N") + (x = "J")) * (y = "N")) _
    + ((x = "V") * (y = "S")) Then flg = 1
If flg = 0 Then Cells(i, "ab").Resize(, 2).Interior.Color = vbYellow
Next i

End Sub

 

[Dazzawm]

Thanks thats sort of ok but I need it to leave alone any other cells that do not have the said criteria.

 

[mirabeau]

Thanks thats sort of ok but I need it to leave alone any other cells that do not have the said criteria.

I don't see how this fits in with your initial request

So like I say if the criteria is not met then I would like the cells highlighted.

Maybe you could provide a bit of sample data showing clearly what you do want and what you don't want.

 

[Dazzawm]

At the moment in column AB it is looking for letters A, J, N & V then there matches listed above in column AC. If there is any other letters in column AB i.e Z, D, W etc.. then the code moves onto the next row.

I think from my limited knowledge of code when it says

If 
((x = "A") * (y = "A")) _
    + (((x = "N") + (x = "J")) * (y = "N")) _
    + ((x = "V") * (y = "S")) Then flg = 1
If flg = 0 Then Cells(i, "ab").Resize(, 2).Interior.Color = vbYellow

it then needs an Else something....

 

[mirabeau]

Please check my post#7 and restate clearly just what it is you want.

 

[Dazzawm]

Please check my post#7 and restate clearly just what it is you want.

Sorry I thought my latest post explained what I wanted. Thanks don't worry about it.