Excel VBA Loop to fill column based on column header

Question

So I have a pivot table that is updated from SQL database everyday. I want to highlight the whole section that days >5, but since data is updated daily, conditional formatting does not work. I created a dynamic range (see below), now I need it to run a loop to find where column 29 (where the days are next to name) is greater then 5 I need everything below to be highlighted in red as my attachment shows. Any help or suggestions? I know this is pretty complex.

CODE:

Sub dynamicRange()

    'Disable certain Excel featured whilst Macro is running
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    'Declare variables
    Dim startCell As Range, lasRow As Long, lastCol As Long, ws As Worksheet

    'Set Objects
    Set ws = Sheet4
    Set startCell = Range("N30")

        'Find last row and column of data
        lastRow = ws.Cells(ws.Rows.Count, startCell.Column).End(xlUp).Row
        lastCol = ws.Cells(startCell.Row, ws.Columns.Count).End(xlToLeft).Column

        'Select dynamic ramge of data
        ws.Range(startCell, ws.Cells(lastRow - 1, lastCol - 1)).Select


    'Re-enable certain Excel features after macro has run
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True

ye-olde-dev · Accepted Answer · 2017-08-19 12:21:30Z

1

Howdee, this should get the job done for you. Just assign the ws variable to the sheet you want to run on. Let me know if you have any questions.

Sub ColorFill()
Dim ws As Worksheet
Dim rngColor As Range, rngHeader
Dim lastRow As Long, lastCol As Long, firstRow, firstCol

'Set Sheet to desired sheet
Set ws = Sheet1

'find top left of range
firstRow = ws.UsedRange.Row
firstCol = ws.UsedRange.Column

'find bottom right of range
lastRow = firstRow + ws.UsedRange.Rows.Count - 1
lastCol = firstCol + ws.UsedRange.Columns.Count - 1

'set range of headers
Set rngHeader = Range(Cells(firstRow, firstCol + 1), Cells(firstRow, lastCol))

'loop through range of headers and color column
For Each cell In rngHeader
If cell.Value > 5 Then
    Set rngColor = Range(cell.Offset(1, 0), Cells(lastRow, cell.Column))
    rngColor.Interior.ColorIndex = 3
End If
Next

End Sub

edited Aug 19, 2017 at 12:21

answered Aug 18, 2017 at 3:22

ye-olde-dev

1,3083 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ctk Kim Over a year ago

Thank you so much. Is there a place where I can set where my header would be? Also, where I can start the top left of my range? For some reason, the code doesn't fill my desired rectangle of red and still has spots. Any suggestions?

ye-olde-dev Over a year ago

I would need to see more of your data to say why it isn't working. I'm guessing you have more than just this table on a tab. You can either move only this table to a tab alone and it will work or define your starting point as you said. Google how to set ranges in vba and it shouldn't be too hard to figure out

Ctk Kim Over a year ago

so When I put this:'Set Sheet to desired sheet Set ws = Sheet4 Set startCell = Range("N29") 'Find last row and column of data lastRow = ws.Cells(ws.Rows.Count, startCell.Column).End(xlUp).Row lastCol = ws.Cells(startCell.Row, ws.Columns.Count).End(xlToLeft).Column 'Select dynamic ramge of data ws.Range(startCell, ws.Cells(lastRow - 1, lastCol - 1)).Select 'set range of headers Set rngHeader = Range(Cells(firstRow, firstCol + 1), Cells(firstRow, lastCol)) the 'Set range of headers section pops up the vba "run-time error" Any advice

ye-olde-dev Over a year ago

at initial glance, I'd say you should change xlUp to xlDown and xlToLeft to xlToRight. Again, it's hard to say without the exact structure of your data.

Collectives™ on Stack Overflow

Excel VBA Loop to fill column based on column header

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related