Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel Macros - Dynamic Range Selection


(!)

TypicalUser's Avatar
TypicalUser TypicalUser is offline
Computer Specs
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2012
Location: Home
Experience: Intermediate
06-Jan-2012, 02:17 AM #1
Excel Macros - Dynamic Range Selection
Having difficulty with the following. I'm thinking it should be simple, but am over-complicating it.

Basically I have a range of data(numbers) that could be any numbers of columns wide and any number of rows deep. There would be no data to the right or below this data and the data normally starts in B10, but depending on the user could be B9 or B11. There are no blank cells in the data, it will be some number or zero.

What I want the code to do:
evaluate the data and conditionally format each cell based on the cell to its immediate right for the whole range of rows and column - 1 (no sense in evaluating last column against blanks). Example: if cell B10 < C10, highlight in light green.

I also want to produce a total at the bottom of each column that would conditionally format the lowest total(s) in yellow

And last, I want a countif total at the bottom of the column I specify (by placing any data in row 1 of that column) that will give me a total of all cells in the column (of data) that is not zero ("<>0") [it has to skip the "totals" row.

I've been racking my brain on this starting with recorded macro and then adding snippets I see in many other responses, and am completely butchering it.

Any help would be greatly appreciated.
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
06-Jan-2012, 07:30 AM #2
"There would be no data to the right or below" is half the crucial information.

The other half is "what about to the left or above?"

That might give us a starting point, facilitating (example):

Range("B" & Rows.Count).End(xlUp).CurrentRegion.Interior.ColorIndex = 3

(note: no physical selection of the range occurs with the above, which might be good ; it depends how you want to "conditionally format")

"I also want to produce a total at the bottom of each column"

Take a while to contemplate this:

Sub test2()
x = Range("B" & Rows.Count).End(xlUp).CurrentRegion.Resize(1, 1).Cells.Row
'get top row
y = Range("B" & Rows.Count).End(xlUp).CurrentRegion.Resize(, 1).Cells.Count
'get rows in range
z = Range("B" & Rows.Count).End(xlUp).CurrentRegion.Columns.Count
'get columns in range
Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, z) = "=SUM(B" & x & ":B" & (x + y - 1) & ")"
'make some sums
End Sub


Welcome to the board.
TypicalUser's Avatar
TypicalUser TypicalUser is offline
Computer Specs
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2012
Location: Home
Experience: Intermediate
06-Jan-2012, 10:27 PM #3
Thanks
Thanks, I understand for the totaling - still confused for the conditional formatting.

Here is what I produce when I record the macro. The problem however is that the macro reads and records a specific cell for the ending point of the array. I want the macro to be dynamic so tha it will work for any array (combination of rows and columns)

Sub test3()
'
' test3 Macro
'
' Keyboard Shortcut: Ctrl+r
'
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=C10"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range("B10:I15").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub


So in my case, my data started at B10 and goes right and down to J15. Since I only want conditional formating on after I do an {End}{Right} I move LEFT one column and paste the format there. I'm sure there is probably a way to do this cleaner than this. I've been trying to use activecell.offset with variables to get the range and inserting variables as arguments for "Formula1:=" but am getting further from my solution.

Last edited by TypicalUser; 07-Jan-2012 at 12:02 AM..
TypicalUser's Avatar
TypicalUser TypicalUser is offline
Computer Specs
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2012
Location: Home
Experience: Intermediate
07-Jan-2012, 12:02 AM #4
Further clarifying:
B10 should validate against C10; C10 against D10, B11 against C11 and so on. Basically copying the relative reference. When I have been trying various methods, I often end up with everything looking at C10 which would be incorrect.
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
07-Jan-2012, 08:59 AM #5
"still confused for the conditional formatting"

I meant it depends whether you want code to "invoke" the built-in conditional formatting function, or do the formatting itself. The latter would be something like:

For Each Cell In Range("B10").CurrentRegion
If Cell < Cell.Offset(, 1) Then
Cell.Interior.ColorIndex = 4
End If
Next Cell
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
07-Jan-2012, 12:50 PM #6
"I've been trying to use activecell.offset with variables to get the range and inserting variables as arguments for "Formula1:=" but am getting further from my solution."

Well then, try a different tack.

1. Use code to figure the range co-ordinates

2. Use those to insert a named range

3. Refer (instead) to the named range while the code's applying the conditional formatting.

HTH

Sub test()
y = Range("B" & Rows.Count).End(xlUp).Row
x = Range("B" & y).End(xlUp).Row
z = Range("B" & y).End(xlToRight).Column

ActiveWorkbook.Names.Add Name:="MyRange", _
RefersToR1C1:="=Sheet1!R" & x & "C2:R" & y & "C" & z

Range("MyRange").FormatConditions.Delete
Range("MyRange").FormatConditions.Add Type:=xlExpression, Formula1:="=B5<C5"
Range("MyRange").FormatConditions(1).Interior.ColorIndex = 6
End Sub


(no selecting involved)
TypicalUser's Avatar
TypicalUser TypicalUser is offline
Computer Specs
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2012
Location: Home
Experience: Intermediate
09-Jan-2012, 01:19 AM #7
Thanks, this is getting me almost all of the way there...

Adding a few of your recommendations, I have this:

Sub newtest()

x = Range("B" & Rows.Count).End(xlUp).CurrentRegion.Resize(1, 1).Cells.Row
'get top row
y = Range("B" & Rows.Count).End(xlUp).CurrentRegion.Resize(, 1).Cells.Count
'get rows in range
Z = Range("B" & Rows.Count).End(xlUp).CurrentRegion.Columns.Count
'get columns in range
Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, Z - 1) = "=SUM(B" & x & ":B" & (x + y - 1) & ")"
'make some sums

ActiveWorkbook.Names.Add Name:="MyRange", _
RefersToR1C1:="=Sheet1!R" & x & "C2:R" & y & "C" & Z

For Each Cell In Range("MyRange").CurrentRegion
If Cell > Cell.Offset(, 1) Then
Cell.Interior.ColorIndex = 6
End If
Next Cell
End Sub


The issue I am having is that my first column contains data (text) that I don't want evaluated in the conditional format loop - same for the last column (will always highlight as its value will nearly always be greater then the empty cell it is next to).

How do I get "MyRange" to be R1C2 to RyC(z-1)?

I tried doing it within the RefersToR1C1, but that appears to not be the place to do that.
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
09-Jan-2012, 01:43 AM #8
If "first column" = A then, we never actually covered "what about to the left or above?".

y = Range("B" & Rows.Count).End(xlUp).Row
x = Range("B" & y).End(xlUp).Row
z = Range("B" & y).End(xlToRight).Column - 1


?
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
dynamic range selection, excel, macro, range

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑