Looping through excel columns

Question

I have NFL stats, in which I'm logging into a table in order to create conditional formatted heat-maps.

Starting in column E and ending in column P is my data, but I need it to start at a2 and go to the last row of that column, and then do the same for every column so the conditions don't overlap and create one large heat-map. I just want a heatmap for every separate column so I can analyze that way. (NOT ALL COLUMNS CONTAIN NUMBERS, AND THEY'RE SPREAD OUT, SO CONDITIONAL FORMATTING SHOULDN'T PICK UP TEXT COLUMNS ANYWAYS)

How would I go about looping the columns without explicitly referencing them? My status for one position won't contain the same amount of columns as another one.

I need this to be as dynamic as possible. Also, can anyone help me clean up the conditional formatting pieces? I just copied over the macro recorded code for that since I don't know how to compile it myself.

I was thinking something like this:

Dim Overall_Stats As Workbook
Dim RB_stats As Worksheet
Set RB_stats = Overall_Stats.Sheets(RB)

LastRow = Range("A" & Rows.Count).End(xlUp).Row

with RB_stats
     .Range("A2:A" & LastRow)
     .FormatConditions.AddColorScale ColorScaleType:=3
     .FormatConditions(.FormatConditions.Count).SetFirstPriority
     .FormatConditions(1).ColorScaleCriteria(1).Type = _
      xlConditionValueLowestValue

With .FormatConditions(1).ColorScaleCriteria     (1).FormatColor
    .Color = 8109667
    .TintAndShade = 0
End With
.FormatConditions(1).ColorScaleCriteria(2).Type = _
    xlConditionValuePercentile
.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With .FormatConditions(1).ColorScaleCriteria(2).FormatColor
    .Color = 8711167
    .TintAndShade = 0
End With
.FormatConditions(1).ColorScaleCriteria(3).Type = _
    xlConditionValueHighestValue
With .FormatConditions(1).ColorScaleCriteria(3).FormatColor
    .Color = 7039480
    .TintAndShade = 0
End With

End With

For i = 1 to 100
    Columns(i).Select
next I

score 1 · Answer 1 · answered Aug 03 '18 at 07:52

I can't help you in conditional formatting, but you can configure it easily (though not quickly) by running the code step by step and check the effect of each step.

For finding the interesting columns in a dynamically changing sheet needs three things to do.

find the rightmost column

Dim lastcol as long
lastcol=RB_stats.usedrange.columns.count

NB: this method is simple and not very reliable but can be used when you have nothing to the right from the last column of data. Finding the rightmost column exactly is another science, see here

determine if a column contains number(s): supposing that the 2nd row contains data, you can test the value in the cell for type:
```
If Typename(Cells(2, col))="Double" Then 
 ... this is a column of numbers, do formatting
```

Typename returns Double for numbers, other values are String, Date, Empty.

apply the formatting on the selected columns

For col=1 to lastcol
     If Typename(Cells(2, col))="Double" Then 
           ' this is a column of numbers, select range for formatting
         LastRow = Cells(Rows.Count, col).End(xlUp).Row
         with RB_stats.Range(Cells(2, col), Cells(LastRow, Col)
           ... do formatting here
         end with
     End If
Next

Looping through excel columns

1 Answers1