Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Programming
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/tutorials...nce-redux.aspx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Programming
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("A2:D100") ' 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. -- Cheers, Ryan "Marat S" wrote: 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/tutorials...nce-redux.aspx . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
New to Excel Programming | Excel Programming | |||
excel programming | Charts and Charting in Excel | |||
programming in excel | Excel Programming | |||
New to programming VB in Excel | Excel Programming | |||
Excel Programming help | Excel Worksheet Functions |