Colour Entire Table Row Based on Values in Two Cells

daithiboy

Board Regular
Joined
Jul 1, 2016
Messages
77
Hi Folks,

I haven't had a chance to use VBA in quite awhile and so I've fallen out of practice.

I have a worksheet with 17,000 rows (growing by approx 1000 a month) and conditional formatting set to change the fill colour of some rows (entire table row) based on the values in column 12 and 13. This is proving very slow and every time I add more data it recalculates.

Seeing as my data won't be changed, I would like to get VBA to run through each row and permanently colour each table row based on what's in columns 12 and 13 i.e. if row x column 12 ="True" AND row x column 13 = "False" THEN colour entire row yellow (entire row = row x column 1 to row x column 13).

I hope that makes sense and if you have any suggestions they would be very much appreciated.

Many thanks,
Dave
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
If got this far,

Code:
Sub colour_rows()


Dim wsAtt As Worksheet
Dim lrow As Long
Dim i As Long


Set wsAtt = ThisWorkbook.Worksheets("All Attendances")
lrow = wsAtt.Cells(Rows.Count, 1).End(xlUp).Row






    With wsAtt
            
            For i = 2 To lrow
                
                If Cells(i, 12) = True And Cells(i, 13) = False Then
                
                    MsgBox "Urgent"
                    
                    ElseIf Cells(i, 12) = True And Cells(i, 13) = True Then
                    
                    MsgBox "Urgent DNA"
                                                        
                End If
                
                
            Next i
                      
    End With




End Sub



I just need to replace the message boxes with instructions to change the row colour.

Thanks a mil,
Dave
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top