Wrox Programmer Forums

Need to download code?

View our list of code downloads.

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
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 .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 9th, 2016, 10:29 PM
Registered User
Points: 12, Level: 1
Points: 12, Level: 1 Points: 12, Level: 1 Points: 12, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2016
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
                 .TintAndShade = 0
                 .PatternTintAndShade = 0
                 End With

        End Select
    End If
        
   Next MYC
    O = O + 1
    Loop
    
End Sub
Reply With Quote
  #2 (permalink)  
Old June 9th, 2016, 11:40 PM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old June 11th, 2016, 06:30 PM
Registered User
Points: 12, Level: 1
Points: 12, Level: 1 Points: 12, Level: 1 Points: 12, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2016
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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,,??
Reply With Quote
  #4 (permalink)  
Old June 12th, 2016, 12:31 PM
Wrox Author
Points: 45, Level: 1
Points: 45, Level: 1 Points: 45, Level: 1 Points: 45, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2011
Location: San Francisco / Silicon Valley
Posts: 11
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old June 15th, 2016, 10:51 PM
Registered User
Points: 12, Level: 1
Points: 12, Level: 1 Points: 12, Level: 1 Points: 12, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2016
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
Reply


Thread Tools
Display Modes

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 Off
Trackbacks 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 01:23 AM
Best way to learn C# ? DonLennon C# 9 March 27th, 2008 10:00 PM
What should I learn first gcjetfan Intro Programming 2 March 14th, 2008 11:38 AM
what languages should i learn? NewProgrammer Intro Programming 4 June 18th, 2006 04:02 AM
is C# easy to learn? Sohail C# 1 July 8th, 2005 04:47 PM



All times are GMT -4. The time now is 11:21 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.