PDA

View Full Version : Solved: Looking up matching cells



Klartigue
02-21-2012, 01:32 PM
Please see attached spreadsheet.

In column G and H, there are words in each cell for each entry in A. I would like to write a vba code that says if the word in column G = the word in column H then in column I write ok (do for each entry). And if cell G does not equal cell H (for each entry), then highlight that entire row (from A:I). And I would like to do this for A7 down until the last row.

Any ideas?

Thanks for the help

CatDaddy
02-21-2012, 01:48 PM
Sub test()
Dim lr As Long, r As Long
Dim cell As Range
Dim str1 As String, str2 As String
ActiveWorkbook.Sheets(1).Activate
lr = Range("A65000").End(xlUp).Row
For Each cell In Range("G7:G" & lr)
r = cell.Row

If (StrComp(cell.Text, cell.Offset(0, 1).Text, vbTextCompare) <> 0) Then

Range("A" & r & ":I" & r).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next cell
End Sub

Klartigue
02-21-2012, 01:57 PM
That works great, thank you!!!

One more question, some of my account numbers in column A are numbers stored as texts, and when i upload a particular excel doc, there is that green arrow in the top left hand corner for these account numbers and I have been having to manually select each one, click the yellow error sign, and select convert to number. Is there a vba that will automatically do this for me?

Now i have tried to format all the account numbers by going to format text and selecting number that doesnt get rid of the error. To solve the error issue I have to manually do it by clicking on the yellow error sign...maybe there is vba code to fix this??

Thanks for the help!!!

CatDaddy
02-21-2012, 02:29 PM
Maybe:
Dim cell as range
For each cell in range("A7:A" & range("A6500").end(xlup).row)
cell.value = CInt(cell.value)
next cell

Klartigue
02-21-2012, 02:40 PM
It says there is a type mismatch with cell.Value = CInt(cell.Value)

CatDaddy
02-21-2012, 02:48 PM
http://support.microsoft.com/kb/291047