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?

 

[Akuini]

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

Is there anyway to share with you my file?

You can upload your workbook somewhere, for example dropbox.com and post the link here

 

[newbie777]

Thanks for the suggestion.

Please find my excel file in the link below
https://www.dropbox.com/s/k0ynntqhenm0251/test.xlsx?dl=0


Can you please help me out?

Typically, i need to compare stock exchanges for each code with the list in column BN. ie each row with column BN

If everything in the row exists in column BN, then put true in Column BI, if one is missing or more, then false.

Thanks in advance.

Please note that you can put it all the exchanges in each row in one cell with semi column as separator. I have been told it is easier to do it this way in VBA.

 

[Nine Zero]

ahhh ok i see why my code was not working now...

I was under the impression that you HAD to have all 64 columns filled out in order to receive true

But you just want it to say true if every cell that does have text, exists in the column regardless of how many there are in the row correct?

 

[newbie777]

EXACTLY YES.

My bad.

Thanks a lot for helping me!

 

[Nine Zero]

Alright try this out i tested it with your test file .. and it worked .. .let me know

Sub Newbie777()
Dim r As Range
Dim rLoc, curCount, lastr, lastc As Integer
Dim lastbx As Integer
lastbx = ActiveSheet.Cells(ActiveSheet.Rows.Count, "BJ").End(xlUp).Row
For Each bx In Range("BJ1:BJ" & lastbx)
    bx.Value = Trim(bx.Value)
Next bx
rLoc = 2
lastr = ActiveSheet.Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row
Do While rLoc <= lastr
curCount = 0
lastc = WorksheetFunction.CountA("C" & rLoc & ":BH" & rLoc)
For Each r In Range("C" & rLoc & ":BH" & rLoc)
    If Application.WorksheetFunction.CountIf(Range("BJ:BJ"), r.Value) > 0 Then
        curCount = curCount + 1
    End If
Next r
If curCount = lastc Then
Range("BI" & rLoc).Value = "True"
Else
Range("BI" & rLoc).Value = "False"
End If
rLoc = rLoc + 1
Loop




End Sub

EDIT: Initially you had said the range would be C to BN and that the data column would be BX but in your test file they were different... the code above is for the test file

if you want to change back to the ranges you had initially requested just change

BJ to BX
BH to BN
BI to BO

 

[newbie777]

https://www.dropbox.com/s/uowptdvrddedfj8/test.xlsx?dl=0

it doesnt work properly still. For example, the second row has NY exchange while column BN doesnt have NY exchange.

I added a link to the update file.

 

[Nine Zero]

i see whats happening... give me a sec while i figure out why its doing what its doing

 

[Nine Zero]

Alright i found it

Sub Newbie777()
Dim r As Range
Dim rLoc, curCount, lastr, lastc As Integer
Dim lastbx As Integer


lastbx = ActiveSheet.Cells(ActiveSheet.Rows.Count, "BJ").End(xlUp).Row
For Each bx In Range("BJ1:BJ" & lastbx)
    bx.Value = Trim(bx.Value)
Next bx


rLoc = 2
lastr = ActiveSheet.Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row


Do While rLoc <= lastr
curCount = 0
lastc = Application.WorksheetFunction.CountA([COLOR=#ff0000]Range([/COLOR]"C" & rLoc & ":BH" & rLoc[COLOR=#ff0000])[/COLOR])
For Each r In Range("C" & rLoc & ":BH" & rLoc)
    If Application.WorksheetFunction.CountIf(Range("BJ:BJ"), r.Value) > 0 Then
        curCount = curCount + 1
    End If
Next r


If curCount = lastc Then
Range("BI" & rLoc).Value = "True"
Else
Range("BI" & rLoc).Value = "False"
End If
rLoc = rLoc + 1


Loop


End Sub

I highlighted in red what was missing in case you were interested

 

[newbie777]

man it works like magic!

Many many thanks!

Have a great day.

 

[Nine Zero]

No problem, glad i could help