VBA - If criteria 'A' is met, AND criteria 'B' is met, format cell accordingly (must use VBA, not conditional formatting)

[MrAussie]

I have read several posts regarding conditional formatting based on a criteria but I cannot seem to apply it to my situation --


I have a table of information, I need this to apply to every row of information.

Assuming first row of information is '4'.. but needs to check all rows in the table with the same criteria. It's an unknown number of rows as it will change each time the data is imported.

If D4 = 0 then M4 must be greater than or equal to 11 but less than 12 else fill with red
If D4 = 0 then N4 must be greater than or equal to 16 but less than 17 else fill with red

If D4 = 1 then M4 must be greater than or equal to 12 but less than 13 else fill with red
if D4 = 1 then N4 must be great than or equal to 17 but less than 18 else fill with red

if D4 = 2 then M4 must be great than or equal to 13 but less than 14 else fill with red
if D4 = 2 then N4 must be greater than or equal to 21 but less than 22 else fill with red

...These are the 3 checks I am trying to apply, but for all rows. So D4 is the beginning but it needs to check D5 (in relation to M5 and N5, etc.)

Any help will be greatly appreciated.

 

[wideboydixon]

It is conditional formatting that you need:

Book1
	D	M	N
4	0	11.9	16.5
5	1	12.5	17.9
6	2	11.9	16.5
7	0	11.9	16.4
8	1	11.9	16.5
9	2	12.5	17.9
Sheet1

Highlight cells D4:D9 and select Conditional Formatting/New Rule... from the ribbon. Select Use a formula to determine which cells to format and then paste this formula in to the box:

=IF(D4=0,IF(AND(AND(M4>=11,M4<12),AND(N4>=16,N4<17)),0,1),IF(D4=1,IF(AND(AND(M4>=12,M4<13),AND(N4>=17,N4<18)),0,1),IF(D4=2,IF(AND(AND(M4>=13,M4<14),AND(N4>=21,N4<22)),0,1),0)))=1

Set the format to be filled with red and click OK. From the sample data above you should have cells D6, D8 and D9 filled with red.

WBD

 

[mathematician]

Conditional formatting applies to your situation but you can do it in VBA also. I'm so tight now but when I get time I'll try to create a simple macro for this

 

[MrAussie]

Thanks very much!!! The reason I am wanting to use VBA is because I want a template the data can be pasted into and then the macro is ready to run. With cond. formatting, when I paste the data (even simply as values without carrying over formatting), it erased the conditional formatting I had set up.

 

[MrAussie]

The reason I am wanting to use VBA is because I want a template the data can be pasted into and then the macro is ready to run. With cond. formatting, when I paste the data (even simply as values without carrying over formatting), it erased the conditional formatting I had set up. Thanks very much for the reply, nonetheless.

 

[wideboydixon]

OK. Here's a simple enough macro that should achieve your requirements in the OP:

Public Sub HighlightColumnD()

Dim lastRow As Long
Dim rowLoop As Long
Dim isRed As Boolean
Dim mValue As Double
Dim nValue As Double

lastRow = Range("D" & Rows.Count).End(xlUp).Row

For rowLoop = 4 To lastRow
    isRed = False
    mValue = Cells(rowLoop, 13).Value
    nValue = Cells(rowLoop, 14).Value
    
    Select Case Cells(rowLoop, 4).Value
        Case 0
            isRed = mValue < 11 Or mValue >= 12 Or nValue < 16 Or nValue >= 17
        Case 1
            isRed = mValue < 12 Or mValue >= 13 Or nValue < 17 Or nValue >= 18
        Case 2
            isRed = mValue < 13 Or mValue >= 14 Or nValue < 21 Or nValue >= 22
    End Select
    
    With Cells(rowLoop, 4).Interior
        If isRed Then
            .Color = RGB(255, 0, 0)
        Else
            .ColorIndex = xlColorIndexNone
        End If
    End With
Next rowLoop

End Sub

WBD

 

[MrAussie]

Thanks very much, this seems to be working fairly well. Although, I was hoping for the values out of range (in column m and n) to be filled red rather than the column D. As is, column D is being filled red. Again, I appreciate your assistance.

 

[MrAussie]

Thanks very much, this seems to be working fairly well. Although, I was hoping for the values out of range (in column m and n) to be filled red rather than the column D. As is, column D is being filled red. Again, I appreciate your assistance.

Update: I have acheived my outcome of filling the values out of range, respectively. If there is a more efficient way of coding this, let me know. Thanks again!!

Public Sub HighlightColumnD()


Dim lastRow As Long
Dim rowLoop As Long
Dim isRedN As Boolean
Dim isRedM As Boolean
Dim mValue As Double
Dim nValue As Double


lastRow = Range("D" & Rows.Count).End(xlUp).Row


For rowLoop = 4 To lastRow
isRedN = False
isRedM = False
mValue = Cells(rowLoop, 13).Value
nValue = Cells(rowLoop, 14).Value

Select Case Cells(rowLoop, 4).Value
Case 0
isRedM = mValue < 11
Case 1
isRedM = mValue < 12
Case 2
isRedM = mValue < 13
End Select

Select Case Cells(rowLoop, 4).Value
Case 0
isRedN = nValue < 16
Case 1
isRed = nValue < 17
Case 2
isRed = nValue < 21
End Select

With Cells(rowLoop, 13).Interior
If isRedM Then
.Color = RGB(255, 0, 0)
Else
.ColorIndex = xlColorIndexNone
End If
End With

With Cells(rowLoop, 14).Interior
If isRedN Then
.Color = RGB(255, 0, 0)
Else
.ColorIndex = xlColorIndexNone
End If
End With

Next rowLoop


End Sub

 

[wideboydixon]

That doesn't fulfill your original requirement of, for example, M4 not being greater than 12 if D4 is zero. I changed my code:

Public Sub HighlightColumnD()

Dim lastRow As Long
Dim rowLoop As Long
Dim isRed(1) As Boolean
Dim mValue As Double
Dim nValue As Double
Dim i As Long

lastRow = Range("D" & Rows.Count).End(xlUp).Row

For rowLoop = 4 To lastRow
    mValue = Cells(rowLoop, 13).Value
    nValue = Cells(rowLoop, 14).Value
    
    Select Case Cells(rowLoop, 4).Value
        Case 0
            isRed(0) = mValue < 11 Or mValue >= 12
            isRed(1) = nValue < 16 Or nValue >= 17
        Case 1
            isRed(0) = mValue < 12 Or mValue >= 13
            isRed(1) = nValue < 17 Or nValue >= 18
        Case 2
            isRed(0) = mValue < 13 Or mValue >= 14
            isRed(1) = nValue < 21 Or nValue >= 22
        Case Else
            isRed(0) = False
            isRed(1) = False
    End Select
    
    For i = 0 To 1
        With Cells(rowLoop, i + 13).Interior
            If isRed(i) Then
                .Color = RGB(255, 0, 0)
            Else
                .ColorIndex = xlColorIndexNone
            End If
        End With
    Next i
Next rowLoop

End Sub

Hope that achieves what you're after.

WBD

 

[MrAussie]

That's much similar, thank you. I had removed the greater than requirement as was too much for our planned outcome. I may implement it again later once I see how this QA check functions. Thanks again very much!