Conditional formatting by code based on different conditions

[olympiac]

I need to format a report on a daily basis:
I need to check the values in three different columns and if it matches the rule below , I need to highligh the row in yellow.

"Rule 1":
If the code in "column A" is "ABCD" or "EFGH" or "IJKL" and the number in "column B" is over "7" and the amount in "column C" is over"1000" or over "-1000" then the row must be highlighted in Yellow
"Rule 2" and "Rule 3" are similar than "Rule 1" with different code and amounts.

How to achieve this by code?

 

[VoG]

Try

Sub fmt()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        If (.Value = "ABCD" Or .Value = "EFGH" Or .Value = "IJKL") And .Offset(, 1).Value > 7 And Abs(.Offset(, 2).Value) > 1000 Then
            .EntireRow.Interior.ColorIndex = 6
        End If
    End With
Next i
End Sub

 

[olympiac]

I have got a Debug due to the headings in the first row.
What do I need to change in the code to start the checking form Row2?

 

[VoG]

Just

Sub fmt()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("A" & i)
        If (.Value = "ABCD" Or .Value = "EFGH" Or .Value = "IJKL") And .Offset(, 1).Value > 7 And Abs(.Offset(, 2).Value) > 1000 Then
            .EntireRow.Interior.ColorIndex = 6
        End If
    End With
Next i
End Sub

 

[olympiac]

Great. Thanks

 

[olympiac]

I have got an additional question.
If I want to format all the other rows that have got different codes in Green, do I need to use something like "If (.Value <> "ABCD" or .Value <> "EFGH" ....?

I tried it but it doesn't work. Can you assist please?

 

[VoG]

Try

Sub fmt()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        If (.Value = "ABCD" Or .Value = "EFGH" Or .Value = "IJKL") And .Offset(, 1).Value > 7 And Abs(.Offset(, 2).Value) > 1000 Then
            .EntireRow.Interior.ColorIndex = 6
        Else
            .EntireRow.Interior.ColorIndex = 4
        End If
    End With
Next i
End Sub

 

[olympiac]

Here is my code. I have 3 rules.

Sub fmt()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("A" & i)
        
If (.Value = "AAAB12" Or .Value = "AAAB16" Or .Value = "ABD12") And .Offset(, 14).Value > 30 Then
            .EntireRow.Interior.ColorIndex = 6
        End If

If (.Value <> "AAAB12" Or .Value <> "AAAB16" Or .Value <> "ABD12") And .Offset(, 14).Value > 7 And Abs(.Offset(, 8).Value) > 1000 Then
            .EntireRow.Interior.ColorIndex = 6
        End If

If (.Value <> "AAAB12" Or .Value <> "AAAB16" Or .Value <> "ABD12") And .Offset(, 14).Value > 90 Then
            .EntireRow.Interior.ColorIndex = 4
        End If

    End With
Next i
End Sub

 

[VoG]

Try

Sub fmt()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("A" & i)
                
        If (.Value = "AAAB12" Or .Value = "AAAB16" Or .Value = "ABD12") And .Offset(, 14).Value > 30 Then
                    .EntireRow.Interior.ColorIndex = 6
        Else
                .EntireRow.Interior.ColorIndex = 4
        End If
        
        If (.Value <> "AAAB12" Or .Value <> "AAAB16" Or .Value <> "ABD12") And .Offset(, 14).Value > 7 And Abs(.Offset(, 8).Value) > 1000 Then
                    .EntireRow.Interior.ColorIndex = 6
        Else
                .EntireRow.Interior.ColorIndex = 4
        End If
        
        If (.Value <> "AAAB12" Or .Value <> "AAAB16" Or .Value <> "ABD12") And .Offset(, 14).Value > 90 Then
                    .EntireRow.Interior.ColorIndex = 4
        Else
                .EntireRow.Interior.ColorIndex = 4
        End If
        
    End With
Next i
End Sub

 

[olympiac]

I appreciate your help however I cannot go for this solution because for the data that doesn't meet the rules it will have to be unformatted.

Any alternative?