Excel Macro VBA basic comparison - but unable to fix error

Question

I have a set of rows and columns range is A:AC and Rows count may be anything

Fixing the sheet and range and calculating RowCount and ColumnCount using this

Dim sheet As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Set sheet = ActiveWorkbook.Worksheets(1)
LastRow = sheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = sheet.Range("A1").CurrentRegion.Columns.Count
Set selectedRange = Range("A1:AC" & LastRow)

First Condition:

I am trying to compare column F to AC should have either 0 or 1. and found that by using

For i =1 To LastRow
   For j = 6 To LastColumn
      If Cells(i, j).Value <> 0 And Cells(i, j) <> 1 Then
           Cells(i, j).Interior.Color = vbGreen
      End If
      If Cells(i, j).Value > 1 Then
           Cells(i, j).Interior.Color = vbRed
      End If
   Next j
Next i

Second Condition

Out of these columns I should have only one column should have 1 and remaining as 0

I tried this and it is throwing different error codes for each run

Third Condition

Find duplicate rows from A:AC

I need to highlight entire row which has the error

But I have done which Column has error with this statement Cells(i, j).Interior.Color = vbRed

Though it is simple I am unable to figure out the logic as I am completely new to Excel VBA.

(a) Note that `Set selectedRange = Range("A1:AC" & LastRow)` is selecting a range on the active sheet, not on the sheet your other statements are accessing. You probably want to say `Set selectedRange = sheet.Range("A1:AC" & LastRow)` - but you don't appear to be using selectedRange elsewhere so it probably doesn't matter. (b) Similarly, all the references to `Cells` in your "first condition" block are accessing the active sheet - you probably need `sheet.Cells`. — YowE3K, Oct 12 '16 at 08:14

score 1 · Accepted Answer · edited May 23 '17 at 11:48

For your first condition:

For i = 1 To LastRow
    For j = 6 To LastColumn
        Select Case Cells(i, j).Value
            Case 0
                Cells(i, j).EntireRow.Interior.Color = vbGreen
            Case 1
                Cells(i, j).EntireRow.Interior.Color = vbRed

            Case Else
                Cells(i, j) = "Whetever"
        End Select
        if range("A" & i).value = 1 and range("AC" & i).value = 1 then
            Cells(i, j).EntireRow.Interior.Color = vbYellow
        end if
   Next j
Next i

Point 2 now added. For deleting duplicates, that depends on your data, but the question has been asked here:

How to look for repeated rows and then delete one of them? requires VBA

I made first condition but in the first one I want to add the second one also instead of adding another set of for loop. — Aravind, Oct 12 '16 at 08:10

Excel Macro VBA basic comparison - but unable to fix error

Though it is simple I am unable to figure out the logic as I am completely new to Excel VBA.

1 Answers1