asked on

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?

Saqib Husain

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 Cox

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

=ISBLANKa1)

holemania

ASKER

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

Saqib Husain

This is not supposed to work "As the cell is modified"

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

holemania

ASKER

Right, but it's not removing the highlight when the file is saved either.

ASKER CERTIFIED SOLUTION

Rodney Endriga

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Husain

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

holemania

ASKER

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?

SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rodney Endriga

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.

holemania

ASKER

Thank you for the help.