skipping text formats
 

Hi:

The following program goes through a column of data and
highlights vlues over a certain constant. The only
problem is that it also highlights the cells with text
also? Can anybody show me how to highlight numbers only?

Thanks in advance for your help!

Sub ChangeCellBasedonValue()
Dim limit As Integer
limit = Range("k1").Value
For Each c In ActiveSheet.Range("e:e").Cells
If c.Value limit Then
With c.Font
.Bold = False
.Italic = False
.ColorIndex = 3
End With
With c.Interior
.ColorIndex = 14
End With
End If
Next c
MsgBox "All done!"
End Sub

skipping text formats
 

One way:-

Sub ChangeCellBasedonValue()
Dim limit As Integer
limit = Range("k1").Value
For Each C In ActiveSheet.Range("e:e").Cells
If C.Value limit And C.Value < 9.999999999E+99 Then
With C.Font
.Bold = False
.Italic = False
.ColorIndex = 3
End With
With C.Interior
.ColorIndex = 14
End With
End If
Next C
MsgBox "All done!"
End Sub

but this would be done so much better by using Conditional formatting which would be
dynamic and change as your data changed without the need for any macros. Also, you could
definitely choose a better colour combination. White font usually works best with a dark
background.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Jay" wrote in message
...
Hi:

The following program goes through a column of data and
highlights vlues over a certain constant. The only
problem is that it also highlights the cells with text
also? Can anybody show me how to highlight numbers only?

Thanks in advance for your help!

Sub ChangeCellBasedonValue()
Dim limit As Integer
limit = Range("k1").Value
For Each c In ActiveSheet.Range("e:e").Cells
If c.Value limit Then
With c.Font
.Bold = False
.Italic = False
.ColorIndex = 3
End With
With c.Interior
.ColorIndex = 14
End With
End If
Next c
MsgBox "All done!"
End Sub



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003

skipping text formats
 

are the values constants?

Sub ChangeCellBasedonValue()
Dim limit As Integer
limit = Range("k1").Value
For Each c In ActiveSheet.Range("e:e").SpecialCells(xlConstants, xlNumbers)
If c.Value limit Then
With c.Font
.Bold = False
.Italic = False
.ColorIndex = 3
End With
With c.Interior
.ColorIndex = 14
End With
End If
Next c
MsgBox "All done!"
End Sub


This should speed up your macro as well, since specialcells restricts itself
to the used range.
--
Regards,
Tom Ogilvy

"Jay" wrote in message
...
Hi:

The following program goes through a column of data and
highlights vlues over a certain constant. The only
problem is that it also highlights the cells with text
also? Can anybody show me how to highlight numbers only?

Thanks in advance for your help!

Sub ChangeCellBasedonValue()
Dim limit As Integer
limit = Range("k1").Value
For Each c In ActiveSheet.Range("e:e").Cells
If c.Value limit Then
With c.Font
.Bold = False
.Italic = False
.ColorIndex = 3
End With
With c.Interior
.ColorIndex = 14
End With
End If
Next c
MsgBox "All done!"
End Sub

skipping text formats
 

Jay,

Add a check for numeric
Isnumeric(c.value) = True

but be aware that it will return True if the cell is blank. But that
shouldn't
be a problem since you are looking for values greater than and blank equates
to 0.
--
sb
"Jay" wrote in message
...
Hi:

The following program goes through a column of data and
highlights vlues over a certain constant. The only
problem is that it also highlights the cells with text
also? Can anybody show me how to highlight numbers only?

Thanks in advance for your help!

Sub ChangeCellBasedonValue()
Dim limit As Integer
limit = Range("k1").Value
For Each c In ActiveSheet.Range("e:e").Cells
If c.Value limit Then
With c.Font
.Bold = False
.Italic = False
.ColorIndex = 3
End With
With c.Interior
.ColorIndex = 14
End With
End If
Next c
MsgBox "All done!"
End Sub