+ Post New Thread
Results 1 to 9 of 9
Coding Thread, VBA 2003 Ranges in Coding and Web Development; OK, I am trying to make a macro in Excel 2003 that changes the background colour of cells depending on ...
  1. #1
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    3,703
    Thank Post
    755
    Thanked 296 Times in 233 Posts
    Blog Entries
    60
    Rep Power
    243

    VBA 2003 Ranges

    OK, I am trying to make a macro in Excel 2003 that changes the background colour of cells depending on the data in them. I have used VBA infrequently since uni and struggling a bit, I am basically recapping over what I can remember while doing this project!

    I have so far got the following code after following an example. I keep getting problems with an error saying Run-Time Error 1004: Method 'Range' of Object '_Global' failed and the debugger points to the line where I am trying saying Set dataArea. Can anyone help a rookie please?

    Code:
    Private Sub ColourChanger()
    MsgBox ("Executed")
    
    Dim dataArea As Range
    Dim strRangeStart As String
    Dim strRangeEnd As String
    
    'strRangeStart = InputBox("Please enter the cell reference for the first cell holding the data you wish to format.")
    'strRangeEnd = InputBox("Please enter the cell reference for the last cell holding data you wish to format.")
    
    Set dataArea = Range("A1:F0")
    
    For Each Cell In dataArea
        If Cell.Value = "" Or IsNumeric(Cell.Value) = False Then
            Cell.Interior.ColorIndex = xlNone
        Else
            Select Case Cell.Value
                Case Cell.Value >= 0
                    Cell.Interior.ColorIndex = 10
                Case Cell.Value = -1
                    Cell.Interior.ColorIndex = 6
                Case Cell.Value <= -1.5
                    Cell.Interior.ColorIndex = 3
                Case Else
                    Cell.Interior.ColorIndex = xlNone
            End Select
        End If
    Next
    
    End Sub

  2. #2

    Join Date
    May 2008
    Location
    Cheshire
    Posts
    270
    Thank Post
    47
    Thanked 27 Times in 24 Posts
    Rep Power
    18
    Hi there,

    You might want to look at 'Conditional Formatting'. With this it'll allow you to format the cells depending on the value. A quick google ('excel 2003 conditional formatting') brought up Conditional Formatting.

    Hope this helps

  3. #3
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    3,703
    Thank Post
    755
    Thanked 296 Times in 233 Posts
    Blog Entries
    60
    Rep Power
    243
    We have tried conditional formatting but not too happy with it. Blank cells get formatted too and if we sort the data, we have to pick through and individually highlight every cell that needs formatting and reapply the formatting rules.

    Was hoping that using a macro, we'll only need to enter a cell range and let the macro do the rest for us.

  4. #4

    Join Date
    May 2008
    Location
    Cheshire
    Posts
    270
    Thank Post
    47
    Thanked 27 Times in 24 Posts
    Rep Power
    18
    Could you just do

    ...
    Range("A1:F10").Select

    For Each Cell In Selection
    ...
    Last edited by Pashers; 15th January 2009 at 03:33 PM. Reason: Forgot For Each Cell in Selection

  5. Thanks to Pashers from:

    CAM (15th January 2009)

  6. #5
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    3,703
    Thank Post
    755
    Thanked 296 Times in 233 Posts
    Blog Entries
    60
    Rep Power
    243
    It works! Been having a clouded up head trying to work out the problem, F0 isn't a valid cell range!

    Thanks for that. I can get on with the rest of it now and I learned a bit more VBA.

  7. #6
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    3,703
    Thank Post
    755
    Thanked 296 Times in 233 Posts
    Blog Entries
    60
    Rep Power
    243
    OK another little predicament. I was able to make the program accept user defined input by taking whatever they highlight in Excel and putting it into a custom range.

    Code:
    Dim rngRange As Range
    Set rngRange = Selection
    Then it does whatever it needs with a loop.

    Code:
    For Each Cell in rngRange
    Fancy code goes here
    Next
    The problem is the range stretches all 65,000 cells to the bottom when a column is selected causing huge delays. I have managed to use a counter to detect when the program hits enough rows to process all the data in the cells. Unfortunately I don't know how to make the program jump to the next column when it does. I tried using the .column property I discovered while watching my custom range but didn't even start the program.

    Google has been the pain as normal giving me everything but what I need. Anyone able to help please?

  8. #7

    Join Date
    May 2008
    Location
    Cheshire
    Posts
    270
    Thank Post
    47
    Thanked 27 Times in 24 Posts
    Rep Power
    18
    Hi, I've made a quick sub routine for you to have quick look at:

    Sub SelectLastUsedMoveToRight()
    Dim r As Range
    Dim last As String
    Set r = Selection
    last = r.Cells(Selection.Rows.Count).Address
    Range(last).End(xlUp).Select
    ActiveCell.Offset(0, 1).Select
    End Sub

    If you select the A coloumn, it'll find the last used cell, then move to the right by one.

    last = r.Cells(Selection.Rows.Count).Address
    This gets the last cell that's selected

    Range(last).End(xlUp).Select
    Gets the last used cell from the selection

    ActiveCell.Offset(0, 1).Select
    Moves the selection to the right.
    Last edited by Pashers; 22nd January 2009 at 02:39 PM. Reason: Explanation of how the sub routine works

  9. Thanks to Pashers from:

    CAM (22nd January 2009)

  10. #8
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    3,703
    Thank Post
    755
    Thanked 296 Times in 233 Posts
    Blog Entries
    60
    Rep Power
    243
    Thanks for that, though I got my columns and rows in a twist. I thought it worked down the columns then moved to the next row, turns out it goes accross the rows then moves down one column so I was barking up the wrong tree! (any other VBA beginners take note of that mistake!)

    I might have to rethink my loop's construction. For Each seems to be too big a pain for such a quick shortcut to getting the macro up and running. Currently capping the macro at 50,000 cells as a stand-in which is still slow but working as a good interim. May swap to a Do While loop with counters for columns and rows since For Each is doing my head in.

    Thanks for the help though.

  11. #9

    Join Date
    May 2008
    Location
    Cheshire
    Posts
    270
    Thank Post
    47
    Thanked 27 Times in 24 Posts
    Rep Power
    18
    Quote Originally Posted by CAM View Post
    We have tried conditional formatting but not too happy with it. Blank cells get formatted too and if we sort the data, we have to pick through and individually highlight every cell that needs formatting and reapply the formatting rules.

    Was hoping that using a macro, we'll only need to enter a cell range and let the macro do the rest for us.
    You don't have to go through each cell to do this.

    In the Conditional formatting section, create a new rule. When you create it using a formula e.g. =$A$1<0. Delete the dollar signs ($) as the dollar signs would enforce it to work only on that one cell. Then when you've created that rule for one cell. You can 'drag' (that little + shape in bottom right of the cell... I don't know the name lol) to the other cells. And then choose fill formatting only.

    So instead of =$A$1<0 it'll be =A1<0.

    If you want, upload it and I'll fix it up for you!

SHARE:
+ Post New Thread

Similar Threads

  1. VBA: Control ID -> function?
    By PiqueABoo in forum Coding
    Replies: 0
    Last Post: 25th November 2008, 03:45 PM
  2. IP Address Ranges
    By Steven in forum Wireless Networks
    Replies: 2
    Last Post: 4th November 2008, 08:48 PM
  3. Excel VBA problems
    By cursong in forum Coding
    Replies: 1
    Last Post: 13th October 2008, 11:28 AM
  4. IP ranges on switches, how do you do it?
    By Kyle in forum Wireless Networks
    Replies: 11
    Last Post: 29th September 2008, 08:17 PM
  5. Looping with range in VBA, help please
    By iuppiter in forum Coding
    Replies: 2
    Last Post: 8th September 2008, 03:47 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •