Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
highlight non blank cells in column
how do I programmically highlight only those cells in a column that contain
data? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight cells where the last charcter in the cell is a blank | Excel Worksheet Functions | |||
highlight highest and lowest cells within a given column in ex | New Users to Excel | |||
highlight highest and lowest cells within a given column in excel | New Users to Excel | |||
Highlight selected cells if text is in certain column | Excel Programming | |||
Highlight blank cells | Excel Programming |