Highlighting empty cells within columns

user_11666863 0
Viewed 82

I am trying to highlight empty cells in columns K,L,M.

I tried the below code

Sub highlight()
    Dim myRange As Range, cel As Range
    Set myRange = Sheet1.Range("K:M")
    For Each cel In myRange
      If Trim(cel.Value) = "" Then cel.Interior.ColorIndex = 3
    Next cel
End Sub

Looking to highlight all the empty cells.

vba
2 Answers

Try:

Sub Color_blank_cells()
'declare variables
  Dim ws As Worksheet
  Dim ColorRng As Range
  Set ws = Worksheets("WorksheetName")
  Set ColorRng = ws.Range("B3:C9")
'color blank cells'
  ColorRng.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(220, 230, 241)

End Sub
user_91488320

Your code appears to work fine, it highlights all the empty cells red. The problem is that you have no way to break out of your loop when you reach the end of your data, the code will continue to highlight empty cells all the way to the end of the sheet (to row 1,048,576) which will likely cause Excel to hang.

You could find the last row of data and break out of the loop when this row is reached. The below limits the loop to the length of column "K" (assumes all columns have the same length). 

Sub highlight()
    Dim myRange As Range, cel As Range
    Set myRange = Sheet1.Range("K:M")
    n = Sheets("Sheet1").Range("K" & Sheets("Sheet1").Rows.Count).End(xlUp).Row
    For Each cel In myRange
      If cel.Row > n Then Exit For
      If Trim(cel.Value) = "" Then cel.Interior.ColorIndex = 3
    Next cel
End Sub
user_38819780
Related