1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macros - Dynamic Range Selection

Discussion in 'Business Applications' started by TypicalUser, Jan 6, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. TypicalUser

    TypicalUser Thread Starter

    Joined:
    Jan 6, 2012
    Messages:
    5
    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.
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    "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. :)
     
  3. TypicalUser

    TypicalUser Thread Starter

    Joined:
    Jan 6, 2012
    Messages:
    5
    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).SetFirstPriority
    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.
     
  4. TypicalUser

    TypicalUser Thread Starter

    Joined:
    Jan 6, 2012
    Messages:
    5
    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.
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    "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
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    "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)
     
  7. TypicalUser

    TypicalUser Thread Starter

    Joined:
    Jan 6, 2012
    Messages:
    5
    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.
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    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


    ?
     
  9. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1034822