Wrox Programmer Forums trying to learn,, HELP!
 Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
June 9th, 2016, 09:29 PM
Registered User
 Points: 12, Level: 1
 Activity: 0%

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
Wrox Author
 Points: 213, Level: 4
 Activity: 0%

Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 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
Registered User
 Points: 12, Level: 1
 Activity: 0%

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
Wrox Author
 Points: 60, Level: 1
 Activity: 0%

Join Date: Apr 2011
Location: San Francisco / Silicon Valley
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
Registered User
 Points: 12, Level: 1
 Activity: 0%

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.

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is OffTrackbacks are Off Pingbacks are On Refbacks are Off

 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

All times are GMT -4. The time now is 10:26 PM.