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

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

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

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