Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code, Please
Hi everyone,
I've been working on this piece of code and I can't find what I'm doing wrong... please if anybody can take a look. -Is not going through B16 to last row (when I'm doing it manualy it works) -Somehow stops on the first 4 rows (is doing together "P" and "Ca"?) -After this it runs to the end Sub Check_High_Values() Dim te As Long 'total elements Dim LastEl As Long 'Last Element Row Dim EndRow As Long 'Ending Row number Dim lastC As String 'Last column letter Dim LasR As Long ' L Dim i As Long Dim oRng As Range Dim myValue As String myfilename = Range("H3").Value te = Range("F6").Value LastEl = (te + 15) EndRow = Range("B6").Value lastC = Range("I100").Value 'look for elements and set high limits based on value With Worksheets("ppb " & myfilename & " data").Range("B16").Select For i = 1 To LastEl 'myValue = valueFind(ActiveCell.Offset(0, 0).Value) ' call function to find value and check columns On Error Resume Next Set oRng = Cells.Find(What:=ActiveCell.Offset(0, 0).Value, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlDown) 'MatchCase:=False) If Not oRng Is Nothing Then If ActiveCell.Offset(0, 0).Value = "P" And _ ActiveCell.Offset(0, 3).Interior.ColorIndex = 6 Then For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC & LastEl) If c.Interior.ColorIndex = 6 And _ c.Value 5000 Then c.Interior.ColorIndex = 44 End If Next ElseIf ActiveCell.Offset(0, 0).Value = "Na" Or _ ActiveCell.Offset(0, 0).Value = "Mg" Or _ ActiveCell.Offset(0, 0).Value = "K" Or _ ActiveCell.Offset(0, 0).Value = "Ca" Or _ ActiveCell.Offset(0, 0).Value = "Fe" And _ ActiveCell.Offset(0, 3).Interior.ColorIndex = 6 Then For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC & LastEl) If c.Interior.ColorIndex = 6 And _ c.Value 5500 Then c.Interior.ColorIndex = 44 End If Next ElseIf ActiveCell.Offset(0, 0).Value < "P" And _ ActiveCell.Offset(0, 0).Value < "Na" And _ ActiveCell.Offset(0, 0).Value < "Mg" And _ ActiveCell.Offset(0, 0).Value < "K" And _ ActiveCell.Offset(0, 0).Value < "Ca" And _ ActiveCell.Offset(0, 0).Value < "Fe" And _ ActiveCell.Offset(0, 3).Interior.ColorIndex = 6 Then ' check the rest For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC & LastEl) If c.Interior.ColorIndex = 6 And _ c.Value 500 Then c.Interior.ColorIndex = 44 End If Next End If End If Next i End With Range("H2").Select End Sub -- gaba :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code, Please
Sorry about this one, I got it working.
Sometimes is good to walk away for a little while. Dave Peterson wrote an answer to another post that help me see what was wrong here.. Thanks Gaba |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
option buttons run Click code when value is changed via VBA code | Excel Programming | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |