CODEWITHSUNDEEP
vba
Starbucks
Starbucks

Reputation: 135

Highlighting empty cells within columns

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.

Upvotes: 2

Views: 176

Answers (2)

DoomedJupiter
DoomedJupiter

Reputation: 73

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

Upvotes: 1

Makhele Sabata
Makhele Sabata

Reputation: 621

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

Upvotes: 1

Related Questions