I am working on my vba exercise and I have two columns L and I. The value in column I depends on column L.

So if column L has value "s" in a row then column I should have value "0" in the same row, otherwise the I, L column should be colored red. If column L has one of the values in array in a row then column I should have nothing in the same row, otherwise the I, L column should be colored red.

The problem is I struggle to make it work in VBA

Also, even if there is a way to do it differently then in VBA I have to do this exercise in VBA.

How can I compare values from the same row that are in two different columns that are not next to each other? Can you help?

Sub validate()

    Dim i As Long
    Set active_sheet = ActiveSheet
    LstRow = active_sheet.Range("I" & active_sheet.Rows.Count).End(xlUp).Row
    Set RngOrders = active_sheet.Range("L2:L" & last_row)
    Set RngPackages = active_sheet.Range("I2:L" & LstRow)
    MValues = Array("M", "kg", "j.m.", "g")
    
    For i = 1 To RngPackages
        If RngOrders(i) = "s" And RngPackages(i) <> "0" Then
            RngPackages(i).Interior.Color = vbRed
        ElseIf RngOrders(i) in MValues And RngPackages(i) <> "" Then
            RngPackages(i).Interior.Color = vbRed
    Next i
    
    
End Sub

CodePudding user response：

Sub validate_someones_homework()

    ' Tools -> References -> Microsoft Scripting Runtime -> check

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Dim MValues As New Scripting.Dictionary
    MValues.Add "M", 0
    MValues.Add "kg", 0
    MValues.Add "j.m.", 0
    MValues.Add "g", 0
    
    Dim r As Long
    For r = 1 To lastRow
        If ws.Cells(r, 12).Value = "s" And Not ws.Cells(r, 9).Value = 0 Then
            ws.Cells(r, 9).Interior.Color = vbRed
        ElseIf MValues.Exists(ws.Cells(r, 12).Value) And Not ws.Cells(r, 9).Value = "" Then
            ws.Cells(r, 9).Interior.Color = vbRed
        End If
    Next r

End Sub