highlight non blank cells in column
 

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

highlight non blank cells in column
 

Hi Annette

See also the VBA help for specialcells for the other Types

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Annette" wrote in message ...
how do I programmically highlight only those cells in a column that contain
data?

highlight non blank cells in column
 

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


"Annette" wrote in message
...
how do I programmically highlight only those cells in a column that
contain
data?

highlight non blank cells in column
 

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

---
HTH
Jason
Atlanta, GA

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


.

highlight non blank cells in column
 

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

---
HTH
Jason
Atlanta, GA

-----Original Message-----
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

---
HTH
Jason
Atlanta, GA

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


.

.

highlight non blank cells in column
 

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 wrote:

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

---
HTH
Jason
Atlanta, GA

-----Original Message-----
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

---
HTH
Jason
Atlanta, GA

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


.

.


--

Dave Peterson

highlight non blank cells in column
 

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

-----Original Message-----
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 wrote:

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

---
HTH
Jason
Atlanta, GA

-----Original Message-----
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

---
HTH
Jason
Atlanta, GA

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


.

.


--

Dave Peterson
.