Excel Macro VBA basic comparison - but unable to fix error

0
votes

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.

1
vbaexcel
range("blah").entirerow for the whole row to start - Preston
(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
you may want to add a "before" and "after" scenarios images - user3598756

1 Answers

1
votes

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