CODEWITHSUNDEEP
vbaexcel
Aravind
Aravind

Reputation: 41571

Excel Macro VBA basic comparison - but unable to fix error

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)

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

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

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.

Upvotes: 0

Views: 60

Answers (1)

Preston
Preston

Reputation: 8187

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

Upvotes: 1

Related Questions