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?
holemaniaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
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

0
Roy CoxGroup Finance ManagerCommented:
You could use Conditional Formatting for this instead of VBA. Apply  formula to create a CF rule like

=ISBLANKa1)
0
holemaniaAuthor 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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Saqib Husain, SyedEngineerCommented:
This is not supposed to work "As the cell is modified"

It is supposed to work "When the file is saved"
0
holemaniaAuthor Commented:
Right, but it's not removing the highlight when the file is saved either.
0
Rodney EndrigaData AnalystCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerCommented:
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

0
holemaniaAuthor 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?
0
Saqib Husain, SyedEngineerCommented:
Change line 6 to

For l = 2 To i
0
Rodney EndrigaData AnalystCommented:
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.
0
holemaniaAuthor Commented:
Thank you for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.