Highlight cells in columns in dynamic range that meet criteria with VBA

maleko1 · Jan 24th 2017

Hello All,

I have a macro that creates a workbook and adds data to sheet 1. The data range is always different with each new created workbook.
In each column with data starting from column D there are values in row 5 (upper tolerance) and values in row 6 (lower tolerance).
From row 10 down to the last row are values that if anyone of them is greater than the value in row 5 or less than the value in row 6 I would like to have the cell highlighted red.
This would apply to any column that has data as well.

I know where I will need to add the code I just don't know how to construct the code to do this.

I understand that this can be done using conditional formatting, but I really need to make this automated.

I have searched and actually posted this similar problem in another forum and not getting the results I need for doing this with VBA so I thought I would try here as well.
Here is the link:
https://www.mrexcel.com/forum/…s-than-dynamic-range.html

Any help that you can give would be very appreciated.
Thank you
Marc

mrmmickle1 · Jan 24th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

If your data appears like this:
[TABLE="width: 448"]

[tr]

[TD="width: 64, align: right"]15[/TD]
[TD="width: 64, align: right"]15[/TD]
[TD="width: 64, align: right"]20[/TD]
[TD="width: 64, align: right"]25[/TD]
[TD="width: 64, align: right"]30[/TD]
[TD="width: 64, align: right"]35[/TD]
[TD="width: 64, align: right"]40[/TD]

[/tr]

[tr]

[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]

[/tr]

[tr]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[/tr]

[tr]

[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[/tr]

[tr]

[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]

[/tr]

[tr]

[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]

[/tr]

[tr]

[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]

[/tr]

[tr]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[/tr]

[tr]

[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[/tr]

[tr]

[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]

[/tr]

[tr]

[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]

[/tr]

[tr]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]

[/tr]

[tr]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[/tr]

[tr]

[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[/tr]

[tr]

[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]

[/tr]

[/TABLE]

Then use code like this:

Code

Sub Test()


'=========================================================================='
'=========================================================================='
'Module Type: Code Module                                                  '
'Purpose    : Cycle through values and highlight red if out of tolerance   '
'=========================================================================='
'Author     : Matt Mickle                                                  '
'LinkedIn   : https://www.linkedin.com/in/matthew-mickle-2101305b          '
'Date       : 01.24.2017                                                   '
'Forum      : OzGrid                                                       '
'URL        : http://www.ozgrid.com/forum/showthread.php?t=202563          '
'=========================================================================='
    
    'Declarations
    Dim LCol    As Integer
    Dim LRow    As Long
    Dim RowLp   As Long
    Dim ColLp   As Long
    Dim UpTol   As Long
    Dim LowTol  As Long
    
    With ActiveSheet
    
        LRow = .Cells(Rows.Count, "D").End(xlUp).Row 'Define Last Row
        LCol = .Cells(5, Columns.Count).End(xlToLeft).Column 'Define Last Column
        
        'Loop through Columns and Rows one at a time
        For ColLp = 4 To LCol
            
            UpTol = .Cells(5, ColLp) 'Define Upper Tolerance
            LowTol = .Cells(6, ColLp) 'Define Lower Tolerance
            
            For RowLp = 10 To LRow
                
                'Test for values out of tolerance
                If .Cells(RowLp, ColLp) > UpTol Or _
                   .Cells(RowLp, ColLp) < LowTol Then
                   
                    .Cells(RowLp, ColLp).Interior.Color = vbRed 'Color Cell Red if it is out of tolerance
            
                End If
                
            Next RowLp
        Next ColLp
    
    End With
    
End Sub

Display More

This will result in highlighting the following cells Red:

[TABLE="width: 448"]

[tr]

[TD="width: 64, align: right"]15[/TD]
[TD="width: 64, align: right"]15[/TD]
[TD="width: 64, align: right"]20[/TD]
[TD="width: 64, align: right"]25[/TD]
[TD="width: 64, align: right"]30[/TD]
[TD="width: 64, align: right"]35[/TD]
[TD="width: 64, align: right"]40[/TD]

[/tr]

[tr]

[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="class: xl65, align: right"]16[/TD]
[TD="class: xl65, align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]17[/TD]
[TD="class: xl65, align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[/tr]

[tr]

[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="class: xl65, align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="class: xl65, align: right"]4.9[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]16[/TD]
[TD="class: xl65, align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]17[/TD]
[TD="class: xl65, align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[/tr]

[tr]

[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="class: xl65, align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="class: xl65, align: right"]4.9[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]17[/TD]
[TD="class: xl65, align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[/tr]

[tr]

[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]

[/tr]

[/TABLE]

maleko1 · Jan 24th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

Yes! That is fantastic! I will try this when I get back to work.
That is exactly the result I am looking for.
I'll let you know how this worked out.
Thank you so much for your time and assistance!

Marc

mrmmickle1 · Jan 24th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

You're Welcome. Keep me posted if you need adjustments.

maleko1 · Jan 25th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

mrmmickle1,

I moved my data down to row 25 and updated your code to apply for this change

Code

For RowLp = 25 To LRow

So I tried running the code and it was highlighting the entire column on some columns starting at row M.
I thought this was strange so I started a fresh sheet and threw in some values and ran the code and it worked fine.
I then copied over some of the data from the created workbook to a new sheet and tried running the code and it wouldn't work.
If I typed in the values it seemed to work fine.

I looked, but could there be something with the formatting of the data on the created workbook that might not allow the code to run properly on this data?

Again, thanks for your help.

Marc

mrmmickle1 · Jan 25th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

Did you leave the Upper and Lower Tolerances on Row 5 and 6? Or did they move too? What about the Column the tolerances start on? If so you need to update these lines as well:

Code

'Remember the Arguments for .Cells go like this .Cells (Row, Column)
       LCol = .Cells(5, Columns.Count).End(xlToLeft).Column '<--------------        
         'Column 4 means Column D 
        For ColLp = 4 To LCol 
             
            UpTol = .Cells(5, ColLp) 'Define Upper Tolerance '<--------------
            LowTol = .Cells(6, ColLp) 'Define Lower Tolerance '<-------------


                 For RowLp = 25 To LRow

Where did you paste the code?

maleko1 · Jan 25th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

Yes, the upper and lower tolerances are still in rows 5 and 6 and column D remains the start column.

Okay, so I have a spreadsheet that has one control button on the sheet. Clicking this button triggers a macro that requires an input of a directory. The directory will have numerous excel files that are all formatted identically. After entering the path to this folder and clicking okay another macro will generate a new workbook and grab each excel file from the directory and sort the data into each row of the new workbook.
I added some code that inserts several rows between the upper and lower tolerances and the data. I also added code that inserts formulas across these additional rows to get min, max, average, standard deviation, Cpk, Ppk etc. of the data below in each column. Lastly, I add some code that will format the decimal place in the entire range to 0.00000.
I put the code you provided in a module and I call the module after all other code has run.

I know your code is running because it is highlighting something, but not correctly. Although, when I tried setting up a new sheet with typing in some random tolerances and data your code works great.
Even if I copy some of the data from the generated sheet and paste the values in the new sheet it will not work unless I type in the values by hand then it will work.
Do you think it could be something with the formatting of the generated data? Any ideas?

Thanks
Marc

mrmmickle1 · Jan 25th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

Change this line to :

Code

With ActiveSheet

Code

With Sheets("YOUR_SHEET_THAT_CONTAINS_DATA'S_NAME_HERE")

maleko1 · Jan 27th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

I tried making that change in the code and unfortunately there was no change.
Below is some code I got from the other forum and it is working, but the problem is I have to set the range large enough to cover any possible number of cells that would contain values. This is leaving highlighted cells in rows below the last row of data.

Code

Sub Macro1()
    Range("D25:EE1000").FormatConditions.Add Type:=xlExpression, Formula1:="=OR(D25>D$5)"
    Range("D25:EE1000").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Range("D25:EE1000").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With




    Range("D25:EE1000").FormatConditions.Add Type:=xlExpression, Formula1:="=OR(D25<D$6)"
    Range("D25:EE1000").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Range("D25:EE1000").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
End Sub

Display More

Do you know how I can adjust this to apply only to the range with data?
I'm not get any replies at the other forum.

I appreciate any help you can provide.

Marc

mrmmickle1 · Jan 30th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

Sure just use this amendment:

Code

Sub Macro1() 
    
    Dim Lrow As Long




    LRow = Cells(Rows.Count, "D").End(xlUp).Row 'Define Last Row




    Range("D25:EE" & Lrow).FormatConditions.Add Type:=xlExpression, Formula1:="=OR(D25>D$5)" 
    Range("D25:EE" & Lrow).FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
    With Range("D25:EE" & Lrow).FormatConditions(1).Interior 
        .PatternColorIndex = xlAutomatic 
        .Color = 255 
        .TintAndShade = 0 
    End With 
     
     
    Range("D25:EE" & Lrow).FormatConditions.Add Type:=xlExpression, Formula1:="=OR(D25<D$6)" 
    Range("D25:EE" & Lrow).FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
    With Range("D25:EE" & Lrow).FormatConditions(1).Interior 
        .PatternColorIndex = xlAutomatic 
        .Color = 255 
        .TintAndShade = 0 
    End With 




End Sub

Display More

maleko1 · Feb 3rd 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

Thank you very much mrmmickle1 for all of your help!

mrmmickle1 · Feb 3rd 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

You're welcome. Glad you got it working.

Highlight cells in columns in dynamic range that meet criteria with VBA

Participate now!

Share