trying to learn,, HELP!

RoLeRodriguez · June 9th, 2016, 09:29 PM

Hello,
i don't know how must be writen the code ,
what i try to do is to find the 2 max and min values of a range(like D2:AJ2) and fill the cell with yellow(max) and green(min) then when is complete checked the range go 1 row down of the same range and do it again until there is no values,,
this is what i have done,, but throw an error or kind of.

Code:

Sub AL()

Dim S As Integer
Dim T As Integer
Dim A As Integer
Dim O As Long
Dim N As Integer
Dim MYR As RANGE
Dim MYC As RANGE

S = ActiveCell.Row
T = Cells(S, 1)
A = Cells(S, 2)
O = 2
N = ActiveCell.Row + 1

Set MYR = RANGE(Cells(O, 4), [ Cells(O, 44)])

RANGE(D2).Select
Do While O <= N
  For Each MYC In MYR
    
    If IsEmpty(MYC) Then
     O = O + 2
    Else

      Select Case MYC
             Case Is = T
                 With Selection.Interior
                 .Pattern = xlSolid
                 .Color = 65535
                 End With

             Case Is = A
                 With Selection.Interior
                 .Pattern = xlSolid
                 .Color = 5287936
                 End With

             Case Else
                 With Selection.Interior
                 .Pattern = xlNone
                 .TintAndShade = 0
                 .PatternTintAndShade = 0
                 End With

        End Select
    End If
        
   Next MYC
    O = O + 1
    Loop
    
End Sub

Tom Urtis · June 9th, 2016, 10:40 PM

The code for this is certainly doable, but can you please explain why you prefer programming code to accomplish this instead of using conditional formatting?

Conditional formatting can also be installed using VBA, but the difference is that you don't need to change the interior color of the cells as your macro does.

I'm asking these questions to help arrive at the best method for a solution based on your situation. You can get what you want either way.

RoLeRodriguez · June 11th, 2016, 05:30 PM

Cuz i dont know how to do it , i look for a way and try a lot but i couldn't,,
if you can ,please tell how,,is almost a week since i try to make it,,
jejeje,, even i got For Dummies,, and nothing happend ,, i mean, i don't catch the idea,,
And the code i wrote,, where is the error,,??
I don't undestand variables very well,,
is there a way a Range can be conditional to the row in use,,with out changing th columns range,,??

tomurtis · June 12th, 2016, 11:31 AM

Just to get things started, assuming...
â¢ your range of numbers begins in column A, in row 2
â¢ it doesn't matter how many rows and columns your range spans
...then this worked for me, using your original approach:

Code:

Sub Test1()

Dim LastRow As Long, LastColumn As Long
Dim xRow As Long
Dim MaxVal As Long, MinVal As Long
Dim rng As Range

LastRow = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Application.ScreenUpdating = False
For xRow = 2 To LastRow
Set rng = Range(Cells(xRow, 1), Cells(xRow, LastColumn))
If WorksheetFunction.CountA(rng) > 2 Then
MaxVal = WorksheetFunction.Max(rng)
MinVal = WorksheetFunction.Min(rng)
rng.Interior.Color = xlNone
rng.Find(What:=MaxVal, LookIn:=xlFormulas, LookAt:=xlWhole).Interior.Color = vbYellow
rng.Find(What:=MinVal, LookIn:=xlFormulas, LookAt:=xlWhole).Interior.Color = vbGreen
End If
Next xRow
Set rng = Nothing
Application.ScreenUpdating = True

End Sub

RoLeRodriguez · June 15th, 2016, 09:51 PM

Hello,, jejeje
thank you very much,,
it really work well, but i had to change it a little bit ,
Now i know better how the codes must be writen,,
Again,, ThankYou.