PDA

View Full Version : Solved: Empty Cell Highlight



drums4monty
08-02-2007, 02:07 AM
I need to check a spreadsheet and highlight any cell that is empty in Col F. The Col will have either M or F or nothing in the cells. Can anyone help? This will go into an already working code on the same sheet.

Alan

Bartek
08-02-2007, 02:54 AM
Hi,


I need to check a spreadsheet and highlight any cell that is empty in Col F. The Col will have either M or F or nothing in the cells. Can anyone help? This will go into an already working code on the same sheet.

Try, for example, something like this (this code highlights cells by changing interior to yellow):


Sub HighligthCells()
Dim Xc As Range
For Each Xc In Range("f1", Range("f65535").End(xlUp))
If Xc.Value = "" Then Xc.Interior.ColorIndex = 6
Next

Range(Range("f65535").End(xlUp).Offset(1,0),"f65535").Interior.ColorIndex = 6
End Sub

Bob Phillips
08-02-2007, 04:23 AM
Sub HiLiteBlanks()
Dim rng As Range

Set rng = Range("F1").Resize(Cells(Rows.Count, "F").End(xlUp).Row)
rng.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=" & rng.Cells(1, 1).Address(0, 0) & "="""""
.FormatConditions(1).Interior.ColorIndex = 3
End With
MsgBox "Number of blanks = " & Application.CountIf(rng, "")
End Sub