Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
nested loops trouble
I'm having a hard time tryig to make this work... any help would be greatly
appreciated I need to find certain value in colum B. If value and interior color(always yellow) match, check through the columns for values bigger than 9999.999 to decrease decimal. If value is bigger than 5000 set interior index to orange (44). Also I need to check if value = #VALUE! to set the interior index to orange. What I'm doing is validating data for a group of elements, whatever doesn't meet the limits set the color to orange... It is possible to do it with nested loops? or do I need to do something else? 'if value is = "P" and Interior.ColorIndex = 6 then 'loop through column 5 to last column 'if value is bigger than 9999.999 then NumberFormat = "0.00" 'If value is bigger than "5000" then Interior.ColorIndex = 44 Next Column Next Row If value = "Na" .... do the check again with different limits I'm working with imported data so I need to find Last Row and Last Column. Thanks in advance -- gaba |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
nested loops trouble
Well, is looping through the rows but not through the columns. I don't know
how to reference to the cells without using "ActiveCell". I'm learning and if I can understand what I'm doing wrong maybe I can fix the problem. Thanks Sub CheckValues() Dim LCol As Long 'Last Column number Dim iCol As Long 'column counter myfilename = Range("H3").Value LCol = Range("E17").End(xlToRight).Column Sheets("ppb " & myfilename & " data").Range("E17").Select Do If ActiveCell.Offset(0, -3).Value = "P" And _ ActiveCell.Offset(0, 0).Interior.ColorIndex = 6 And _ ActiveCell.Offset(0, 0).Value "5000" Then For iCol = 1 To LCol ActiveCell.Offset(0, 0).Select Selection.Interior.ColorIndex = 44 Next iCol ' ElseIf ActiveCell.Offset(0, -3).Value = "Na" And _ 'ActiveCell.Offset(0, 0).Interior.ColorIndex = 6 And _ 'ActiveCell.Offset(0, 0).Value "5500" Then 'ActiveCell.Offset(0, 0).Select 'Selection.Interior.ColorIndex = 44 'ActiveCell.Offset(1, 0).Select 'ElseIf ActiveCell.Offset(0, -3).Value = "Mg" And _ 'ActiveCell.Offset(0, 0).Interior.ColorIndex = 6 And _ 'ActiveCell.Offset(0, 0).Value "500" Then 'ActiveCell.Offset(0, 0).Select 'Selection.Interior.ColorIndex = 44 'ActiveCell.Offset(1, 0).Select End If ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, -3)) End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
nested loops trouble
I don't think you need nested loops to make this work (if i understan your question correctly). You simply want to look through column B fo either value errors or for values over 5000 and if found, make th cells orange? Dim PaintColor As Integer Dim RowCount As Long Dim InterestedCol As Integer Dim MyCell As Object On Error Resume Next PaintColor = 46 InterestedCol = 2 For RowCount = 1 To UsedRange.Rows.Count Set MyCell = Cells(RowCount, InterestedCol) If IsError(MyCell.Value) Or MyCell.Value 5000 Then MyCell.Interior.ColorIndex = PaintColor End If Next RowCoun -- crispb ----------------------------------------------------------------------- crispbd's Profile: http://www.excelforum.com/member.php...fo&userid=1088 View this thread: http://www.excelforum.com/showthread.php?threadid=27407 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
nested loops trouble
Thanks crispbd for your answer. I'm looking for different values in column B,
if value is = (P, Na, Ca, etc.) then look through the columns and check for the upper limits ColB P 'Check if values are 5000 Na 'Check if values are 5500 Ca 'Check if values are 500 if value is bigger than limit, set interior.ColorIndex to 44 My problem is once I found the value in column B (by looping through the rows) check values through the columns. I started thinking I'm using the wrong approach... "crispbd" wrote: I don't think you need nested loops to make this work (if i understand your question correctly). You simply want to look through column B for either value errors or for values over 5000 and if found, make the cells orange? Dim PaintColor As Integer Dim RowCount As Long Dim InterestedCol As Integer Dim MyCell As Object On Error Resume Next PaintColor = 46 InterestedCol = 2 For RowCount = 1 To UsedRange.Rows.Count Set MyCell = Cells(RowCount, InterestedCol) If IsError(MyCell.Value) Or MyCell.Value 5000 Then MyCell.Interior.ColorIndex = PaintColor End If Next RowCount -- crispbd ------------------------------------------------------------------------ crispbd's Profile: http://www.excelforum.com/member.php...o&userid=10880 View this thread: http://www.excelforum.com/showthread...hreadid=274070 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with nested for loops | Excel Discussion (Misc queries) | |||
Help with nested for loops | Excel Worksheet Functions | |||
Max limit of 7 nested loops | Excel Worksheet Functions | |||
Help on nested loops | Excel Programming | |||
Nested loops?? | Excel Programming |