We help IT Professionals succeed at work.

Excel - VBA Highlight Cells

I have an excel spreadsheet that will highlight the cells from B:N that's empty if column A is populated.  I would like it to un-highlight any cells in that row if it's populated as well as clear all highlighted once A is empty.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i, l, r As Range, cnt

cnt = 0


i = ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
  For l = 1 To i
    If ThisWorkbook.Sheets(1).Range("A" & 1).Value <> "" Then
      Set r = ThisWorkbook.Sheets(1).Range("B" & 1 & ":N" & 1)
        For Each Cell In r
          If IsEmpty(Cell) Then
            r.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6
            cnt = cnt + 1
            Cancel = True
            Exit For
          Else
            r.Interior.ColorIndex = xlNone
        End If
      Next
    End If
  Next l
  

If cnt > 0 Then
    MsgBox ("There are required cells that needs to be filled.")
End If
  
End Sub

Open in new window


The above is working, but the un-highlight isn't working correctly.  I have a header and it clear that.  It does not clear any other rows afterward.  Am I missing something?
Comment
Watch Question

Try this modification. I have not tested it
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i, l, r As Range, cnt

cnt = 0


i = ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
  For l = 1 To i
      Set r = ThisWorkbook.Sheets(1).Range("B" & 1 & ":N" & 1)
        For Each Cell In r
          If IsEmpty(Cell) Then
    If ThisWorkbook.Sheets(1).Range("A" & 1).Value <> "" Then
            r.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6
            cnt = cnt + 1
            Cancel = True
            Exit For
          Else
            r.Interior.ColorIndex = xlNone
        End If
    End If
      Next
  Next l
  

If cnt > 0 Then
    MsgBox ("There are required cells that needs to be filled.")
End If
  
End Sub

Open in new window

Roy CoxGroup Finance Manager

Commented:
You could use Conditional Formatting for this instead of VBA. Apply  formula to create a CF rule like

=ISBLANKa1)

Author

Commented:
Saqib,

I tried what you had modified, and it's not removing the highlighted as I populate each cell.

Roy,

That would work to remove all the highlighted cell if, column A is empty.  I had actually already used a conditional formatting that is like this.

=$A1:$A9999 = "",  = $B:$N,  clear
This is not supposed to work "As the cell is modified"

It is supposed to work "When the file is saved"

Author

Commented:
Right, but it's not removing the highlight when the file is saved either.
Data Analyst
Commented:
You can try this code:
Dim i, l, r As Range, cnt
Application.ScreenUpdating = False
cnt = 0

i = ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
  For l = 1 To i
    Set r = ThisWorkbook.Sheets(1).Range("B" & l & ":N" & l)
    If IsEmpty(ThisWorkbook.Sheets(1).Range("A" & l).Value) = False Then
        For Each Cell In r
          If IsEmpty(Cell) Then
            r.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6
            cnt = cnt + 1
          Else
            r.Interior.ColorIndex = xlNone
        End If
      Next
    Else
        r.Interior.ColorIndex = xlNone
    End If
  Next l
  
If cnt > 0 Then
    MsgBox ("There are required cells that needs to be filled.")
End If
Application.ScreenUpdating = True

Open in new window


I just altered your existing code a bit to get the results you are requesting. Let me know if you are looking for these type of results.
I have done some testing on this one
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i, l, r As Range, cnt

cnt = 0


i = ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
  For l = 1 To i
      Set r = ThisWorkbook.Sheets(1).Range("B" & l & ":N" & l)
        For Each cell In r
          If IsEmpty(cell) Then
            If ThisWorkbook.Sheets(1).Range("A" & i).Value <> "" Then
                cell.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6
                cnt = cnt + 1
                Cancel = True
                'Exit For
            Else
            cell.Interior.ColorIndex = xlNone
            End If
        Else
            cell.Interior.ColorIndex = xlNone
        End If
      Next cell
  Next l
  

If cnt > 0 Then
    MsgBox ("There are required cells that needs to be filled.")
End If
  
End Sub

Open in new window

Author

Commented:
Rodney,

your example is working, but is there a way to avoid the first row since that's my header column and I have some of the title highlighted?
Change line 6 to

For l = 2 To i
Rodney EndrigaData Analyst

Commented:
Yes, I agree with Syed.

Change line 6 FROM:

    For l = 1 To i

TO:

     For l = 2 To i

This change will apply the highlighting/unhighlighting starting on Row 2 to the end of your data rows.

Author

Commented:
Thank you for the help.