Wrox Programmer Forums trying to learn,, HELP!
 |
BOOK: Excel VBA 24-Hour Trainer 2nd edition
 This is the forum to discuss the Wrox book Excel VBA 24-Hour Trainer 2nd Edition by Tom Urtis; ISBN: 978-1-118-99137-4 Read more about Excel VBA 24-Hour Trainer 2nd Edition or buy the book from your favorite retailerDownload the code for Excel VBA 24-Hour Trainer 2nd Edition
 Welcome to the p2p.wrox.com Forums. You are currently viewing the BOOK: Excel VBA 24-Hour Trainer 2nd edition section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

June 9th, 2016, 09:29 PM
 RoLeRodriguez Registered User Join Date: Jun 2016 Posts: 3 Thanks: 0 Thanked 0 Times in 0 Posts
trying to learn,, HELP!

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
End With

End Select
End If

Next MYC
O = O + 1
Loop

End Sub```

June 9th, 2016, 10:40 PM
 Tom Urtis Wrox Author Join Date: Jan 2015 Posts: 35 Thanks: 0 Thanked 6 Times in 6 Posts

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.

June 11th, 2016, 05:30 PM
 RoLeRodriguez Registered User Join Date: Jun 2016 Posts: 3 Thanks: 0 Thanked 0 Times in 0 Posts

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,,??

June 12th, 2016, 11:31 AM
 tomurtis Wrox Author Join Date: Apr 2011 Posts: 16 Thanks: 0 Thanked 2 Times in 2 Posts

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```

June 15th, 2016, 09:51 PM
 RoLeRodriguez Registered User Join Date: Jun 2016 Posts: 3 Thanks: 0 Thanked 0 Times in 0 Posts

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.

 Similar Threads Thread Thread Starter Forum Replies Last Post Where to learn libcurl? krishbala All Other Wrox Books 3 July 2nd, 2013 12:23 AM Best way to learn C# ? DonLennon C# 9 March 27th, 2008 09:00 PM What should I learn first gcjetfan Intro Programming 2 March 14th, 2008 10:38 AM what languages should i learn? NewProgrammer Intro Programming 4 June 18th, 2006 03:02 AM is C# easy to learn? Sohail C# 1 July 8th, 2005 03:47 PM