Selecting variable ranges using VBA

[VBilgen]

Hello,

I have to run a fairly tedious task of conditional formatting for large tables. The catch is that I have to run this formatting one column at a time and within sections. Obviously in small datasets, this is fine, but I am trying to get vba to do this for me since my tables can get quite large. For Example, I would want conditional formatting to show the highest Truth Score and the Highest Energy Score for VPs, and then for directors and then for Managers, and then for analysts:

[TABLE="width: 500"]
<tbody>[TR]
[TD](average for each)[/TD]
[TD]VP[/TD]
[TD]Director[/TD]
[TD]Manager[/TD]
[TD]Analyst[/TD]
[/TR]
[TR]
[TD]Truth Question 1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Truth Question 2
[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Truth Question 3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Energy Question 1[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Energy Question 2[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Energy Question 3[/TD]
[TD]2[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Energy Question 4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

In the example above, I start with Truth Questions for VPs (3 cells), and apply formatting. Then I go to Directors, etc. After truth questions are done, I do the same for the 4 energy questions.

Each time I make these tables the number of questions per topic could change, as well as the number of breakouts (maybe 5 locations instead of by Position, etc). I would like to be able to just highlight a whole table and then run some vba and it will walk through the table for me, instead of having to manually create something like the above each time. Also, the actual types of conditional formatting I will be doing are more complex, but I know how to make those using vba. I just need help correctly finding the formatting ranges and looping through.

I greatly appreciate you pointing me in the right direction, as I am having a tough time getting this off the ground, although I know it's possible...
Thanks!

 

[VBilgen]

Hello,

As a follow up, I've figured out how to highlight one section (i.e. All truth questions from VP to Analyst) and have the code step through each column to apply conditional formatting:

Dim lRow As LongDim rngObj As Range
Dim rngString As String


Set rngObj = Selection


FirstRow = rngObj.Row
LastRow = FirstRow + rngObj.Rows.Count - 1


FirstCol = rngObj.Column
LastCol = FirstCol + rngObj.Columns.Count - 1


For lCol = FirstCol To LastCol
    Worksheets("Sheet1").Range(Cells(FirstRow, lCol), Cells(LastRow, lCol)).Select
    Call HeatMap
Next lCol


End Sub

This still leaves me with having to select all Truth questions, run code, then select all Energy questions and run macro. If there is a way to select all data and have the code differentiate between the two section, I am very interested. Thank you again for your help!

 

[C Moore]

I've started using the cells property quite a bit, because I define row and column with a number. Mixing it with range to define selection/array corners has simplified things for me. The following code will set your arrays and then do what you need to with each array. (I subbed changing interior colors to make sure it ran correctly, so ignore that.) This assumes answers start in column 2, adjust as need be, and contiguous cells in the table (no blanks).

Sub select_ranges()
    truthfirstrow = Cells.Find(what:="Truth*", After:=[A1], Searchorder:=xlNext).Row
    energyfirstrow = Cells.Find(what:="Energy*", After:=[A1], Searchorder:=xlNext).Row
    lastcol = Range("A1").End(xlToRight).Column
    
    Set trutharray = Range(Cells(truthfirstrow, 2), Cells(energyfirstrow - 1, lastcol))
    Set energyarray = Range(Cells(energyfirstrow, 2), Cells(energyfirstrow, 2).End(xlDown).End(xlToRight))
    
    With trutharray.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    With energyarray.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
End Sub