highlight non blank cells in column

[Annette]

how do I programmically highlight only those cells in a column that contain
data?

 

[Ron de Bruin]

Hi Annette

See also the VBA help for specialcells for the other Types

On Error Resume Next
Columns("A").Cells.SpecialCells(xlCellTypeConstants).Select
On Error GoTo 0

 

[Ray Costanzo [MVP]]

Here's one way. In the testit sub, I'm passing a column as a range, which
would do what you wanted to do, but you could run the NonemptyHighlight
against any range, not just a column.

Sub testit()
Dim x As Range
Set x = ActiveSheet.Columns("A:A")
Call NonemptyHighlight(x)
Set x = Nothing
End Sub

Sub NonemptyHighlight(theRange As Range)
theRange.FormatConditions.Add xlCellValue, xlNotEqual, "="""""
theRange.FormatConditions(1).Interior.ColorIndex = 4
End Sub

Ray at work

 

[Jason Morin]

Sub Test()
Dim rngCol As Range
Dim rngC As Range
Dim rngF As Range
Dim rngCF As Range

Set rngCol = Columns("A:A")
Set rngC = rngCol.SpecialCells(xlCellTypeConstants)
Set rngF = rngCol.SpecialCells(xlCellTypeFormulas)
Set rngCF = Union(rngC, rngF)

rngCF.Select

End Sub

 

[Jason Morin]

With error-trapping:

Sub Test()
Dim rngCol As Range
Dim rngC As Range
Dim rngF As Range
Dim rngCF As Range

Set rngCol = Columns("A:A")
On Error GoTo ErrorFound
Set rngC = rngCol.SpecialCells(xlCellTypeConstants)
Set rngF = rngCol.SpecialCells(xlCellTypeFormulas)
Set rngCF = Union(rngC, rngF)

rngCF.Select

Exit Sub

ErrorFound:
MsgBox "No cells found."

End Sub

 

[Dave Peterson]

I like your idea, but you'll get an error in only constants or only formulas are
in that range.

maybe...

Option Explicit

Sub Test()
Dim rngCol As Range
Dim rngC As Range
Dim rngF As Range
Dim rngCF As Range

Set rngCol = Columns("A:A")
On Error Resume Next
Set rngC = rngCol.SpecialCells(xlCellTypeConstants)
Set rngF = rngCol.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If rngC Is Nothing Then
Set rngCF = rngF
ElseIf rngF Is Nothing Then
Set rngCF = rngC
Else
Set rngCF = Union(rngC, rngF)
End If

If rngCF Is Nothing Then
MsgBox "no formulas or constants in that range!"
Else
rngCF.Select
End If

Exit Sub

 

[Jason Morin]

Thanks Dave. I didn't test my macro with only constants
or only formulas in the col. A.