Excel Programming

[Marat S]

I work with reports which look like charts with a first row that contains names of the columns and following rows contain either number or word.
So I need to find cells with wrong values.
For example, a cell has to contain a number, but it might be either not filled or have a wrong value (too big or too small).
and I need VBA to help me find those kinds of celss and give me the report ID number which is the 1st column.
and color those cells.
Can anyone help!? REALLY IMPORTANT!


Submitted via EggHeadCafe - Software Developer Portal of Choice
Bamboo Prevalence Redux: BAMBlog Blogger App
http://www.eggheadcafe.com/tutorial...ab0-d56baee5f2b0/bamboo-prevalence-redux.aspx

 

[Ryan H]

Here is some rough code that I think will work for you. You didn't specify
the range address you wish to search or specific values that are too large or
too small or the cell location of the Report ID, so I had to make some
assumptions. You will have to tweak the numbers to fit your needs. I hope
this helps! If so, let me know, click "YES" below.

Sub FindBadCells()

Dim c As Range
Dim MyRange As Range

' set range to scan
Set MyRange = Range("A2100")

' scan each cell in the range
For Each c In MyRange
With c

' reset interior color index
.Interior.ColorIndex = xlNone

' if cell isn't a number highlight yellow
If Not IsNumeric(.Value) Then
.Interior.Color = 65535
End If

' if cell number is too big highlight orange
If IsNumeric(.Value) And .Value > 100 Then
.Interior.Color = 49407
End If

' if cell number is too big highlight red
If IsNumeric(.Value) And .Value < 50 Then
.Interior.Color = 255
End If
End With
Next c

' show report id
MsgBox "Report ID is " & Range("A1").Value

End Sub

I would also recommend you use Data Validation to change the colors of you
cells which would probably be better than code. Just a thought.