Wrox Programmer Forums
|
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 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
 
Old June 9th, 2016, 09:29 PM
Registered User
 
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
 
Old June 9th, 2016, 10:40 PM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 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.
 
Old June 11th, 2016, 05:30 PM
Registered User
 
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,,??
 
Old June 12th, 2016, 11:31 AM
Wrox Author
 
Join Date: Apr 2011
Posts: 16
Thanks: 0
Thanked 2 Times in 2 Posts
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
 
Old June 15th, 2016, 09:51 PM
Registered User
 
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.





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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.