VBA data compare

[newbie777]

Hi all,

this is my first post so pardon me if it is not complete. You can ask me for more info if needed.

So i have basically data in consecutive rows that i need to compare with data in a column

If the data of each cell in each row exists in the column, then i need the row to be red. Obviously, if one missing cell, i need it to be green.

The data that i need to check is from C10:BN300
The column that i need to check against is BX:BX

Please note that i need to check each cell in each row against this column as every row is treated. And the aim is to check if this row is all good or not.


Can anyone please help me with this?

 

[Nine Zero]

Let me see if i understand this correctly , I will make example with smaller data for size purposes



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AA[/TD]
[TD]BB[/TD]
[TD]CC[/TD]
[TD]DD[/TD]
[TD]EE[/TD]
[TD]this row would be red[/TD]
[TD][/TD]
[TD][/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]PP[/TD]
[TD]QQ[/TD]
[TD]RR[/TD]
[TD]SS[/TD]
[TD]TT[/TD]
[TD]this row would be green[/TD]
[TD][/TD]
[TD][/TD]
[TD]BB[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]AA[/TD]
[TD]BB[/TD]
[TD]QQ[/TD]
[TD]PP[/TD]
[TD]SS[/TD]
[TD]this row would be red[/TD]
[TD][/TD]
[TD][/TD]
[TD]CC[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DD[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PP[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]QQ[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SS[/TD]
[/TR]
</tbody>[/TABLE]


is that correct?

 

[newbie777]

That is perfectly correct. If one cell is not matching any value in the column, then this is a false. Instead of coloring the row, if we can put in column G TRUE or FALSE beside each row. Then this would be even better.

Many thanks!

 

[newbie777]

And if we can use an array, it would be magical.

 

[Nine Zero]

Alright got this for you... tested and works for me lol

Sub Newbie777()
Dim r As Range
Dim rLoc, curCount, lastr As Integer
rLoc = 10
lastr = ActiveSheet.Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row
Do While rLoc <= lastr
curCount = 0
For Each r In Range("C" & rLoc & ":BN" & rLoc)
    If Application.WorksheetFunction.CountIf(Range("BX:BX"), r.Value) > 0 Then
        curCount = curCount + 1
    End If
Next r
If curCount = 64 Then
Range("BO" & rLoc).Value = "True"
Else
Range("BO" & rLoc).Value = "False"
End If
rLoc = rLoc + 1
Loop


End Sub

Remember to save your file before doing this or trying it in a copy of the original

 

[Nine Zero]

you could also try this if the data will always be up to row 300

Sub Newbie777()
Dim r As Range
Dim rLoc, curCount, lastr As Integer
rLoc = 10
lastr = 300
Do While rLoc <= lastr
curCount = 0
For Each r In Range("C" & rLoc & ":BN" & rLoc)
    If Application.WorksheetFunction.CountIf(Range("BX:BX"), r.Value) > 0 Then
        curCount = curCount + 1
    End If
Next r
If curCount = 64 Then
Range("BO" & rLoc).Value = "True"
Else
Range("BO" & rLoc).Value = "False"
End If
rLoc = rLoc + 1
Loop




End Sub

 

[newbie777]

Thank you so much.

However, for some reason it flags everything as false.
I have one row that has NasdaQ stock Exchange which is present in the column but it shows it as false.

 

[Nine Zero]

Hmmm try verifying that on both the row and the column they are identical... meaning no extra spaces at the end or the beginning and that the capitalization is the same... if you dont wanna check all of them, which i understand as it is alot of data, ill try and modify it to check those as well.. give me a sec

 

[Nine Zero]

try this one out

Sub Newbie777()
Dim r As Range
Dim rLoc, curCount, lastr As Integer
Dim bx As Range
Dim lastbx As Integer
lastbx = ActiveSheet.Cells(ActiveSheet.Rows.Count, "BX").End(xlUp).Row
For Each bx In Range("BX1:BX" & lastbx)
    bx.Value = Trim(bx.Value)
Next bx

rLoc = 10
lastr = 300
Do While rLoc <= lastr
curCount = 0
For Each r In Range("C" & rLoc & ":BN" & rLoc)
    If Application.WorksheetFunction.CountIf(Range("BX:BX"), r.Value) > 0 Then
        curCount = curCount + 1
    End If
Next r
If curCount = 64 Then
Range("BO" & rLoc).Value = "True"
Else
Range("BO" & rLoc).Value = "False"
End If
rLoc = rLoc + 1
Loop


End Sub

 

[newbie777]

Still doesnt work for some reason. But thanks for trying!

Is there anyway to share with you my file?