VBA 2003 Ranges

[CAM]

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?

 

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

[Pashers]

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

[CAM]

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.

[Pashers]

Could you just do

 

...

Range("A1:F10").Select

 

For Each Cell In Selection

...

Edited January 15, 2009 by Pashers
Forgot For Each Cell in Selection

[CAM]

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.

[CAM]

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.

 

Dim rngRange As Range
Set rngRange = Selection

 

Then it does whatever it needs with a loop.

 

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?

[Pashers]

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.

Edited January 22, 2009 by Pashers
Explanation of how the sub routine works

[CAM]

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.

[Pashers]

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!