VBA - Using AND for conditonal formattiing

[fluffynicesheep]

Hi,

I currently have some vba code that looks at column BM and if the cell is empty then it sets the interior colour to white for all cells in the row (from BM to BZ). If there is something in the cell in column BM then it changes everything in the above range to yellow.

What I want to add to this (but have no idea how) is - if the cell in column BM is not empty, but one of the cells (in the same row) from BN to BZ is empty, then it turns that particular cell to red (with rest staying yellow)

Here's what I have at the moment:

Sub Colour()
   Dim i As Long, r1 As Range, r2 As Range
Dim LastRow As Long
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
  
   For i = 2 To LastRow
      Set r1 = Range("BM" & i)
      Set r2 = Range("BM" & i & ":BZ" & i)
      If r1.Value = "" Then r2.Interior.Color = vbWhite
      If r1.Value <> "" Then r2.Interior.Color = vbYellow
   
   Next i
End Sub

Any help would be greatly received!!

 

[Fluff]

How about

Sub Colour()
   Dim i As Long, r1 As Range, r2 As Range
   Dim LastRow As Long
   LastRow = Range("B" & Rows.Count).End(xlUp).Row
  
   For i = 2 To LastRow
      Set r2 = Range("BM" & i & ":BZ" & i)
      If Range("BM" & i) = "" Then
         r2.Interior.Color = vbWhite
      Else
         r2.Interior.Color = vbYellow
         r2.SpecialCells(xlBlanks).Interior.Color = vbRed
      End If
   
   Next i
End Sub

 

[fluffynicesheep]

Hi Fluff,

Unfortunately when running this i get a run-time error 1004 - No cells were found.

It then highlights the line:

r2.SpecialCells(xlBlanks).Interior.Color = vbRed

This may be because there may not always be blank cells in the range (ideally there won't be, as a blank cell means someone hasn't filled in what I need them to fill in!)

 

[Fluff]

Ok, how about

Sub Colour()
   Dim i As Long, r1 As Range, r2 As Range
   Dim LastRow As Long
   LastRow = Range("B" & Rows.Count).End(xlUp).Row
  
   For i = 2 To LastRow
      Set r2 = Range("BM" & i & ":BZ" & i)
      If Range("BM" & i) = "" Then
         r2.Interior.Color = vbWhite
      Else
         r2.Interior.Color = vbYellow
         On Error Resume Next
         r2.SpecialCells(xlBlanks).Interior.Color = vbRed
         On Error GoTo 0
      End If
   Next i
End Sub

 

[fluffynicesheep]

That's perfect

Thank you

 

[Fluff]

You're welcome & thanks for the feedback.