VBA to colour cell background rather than use conditional formatting

I have a worksheet that returns several hundred lines of data. I need code that will look at the first two characters of each row in column C and if the criteria is met colour the cell background of the whole row. Normally I would use conditional formatting e.g =LEFT($C$2,2)="AA" however I have more than 3 criteria and colour requirments.  The number of rows in the worksheet are variable and colums are fixed. Thank you.



sq30Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
>>Normally I would use conditional formatting e.g =LEFT($C$2,2)="AA" however I have more than
>>3 criteria and colour requirments.

How many conditions do you have?  If it is four, you can still use CF; set the background color normally for your 4th condition, and then use your 3 CF conditions to override that color.

If you need five or more conditions, then you will need code.

Are the values in Col C being entered via keyboard, or are they determined via formula?
0
Patrick MatthewsCommented:
Here is an example for code when you need >4 conditions, and the data are actually entered in Column C.

The code goes into the "sheet" module for the worksheet you need watched.


Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cel As Range
    
    If Not Intersect(Target, Me.[c:c]) Is Nothing Then
        Application.EnableEvents = False
        For Each cel In Intersect(Target, Me.[c:c]).Cells
            Select Case UCase(Left(cel, 2))
                Case "": cel.EntireRow.Interior.ColorIndex = xlColorIndexNone
                Case "AA": cel.EntireRow.Interior.Color = vbRed
                Case "BB": cel.EntireRow.Interior.Color = vbBlue
                Case "CC": cel.EntireRow.Interior.Color = vbGreen
                Case "DD": cel.EntireRow.Interior.Color = vbBlack
                Case "EE": cel.EntireRow.Interior.Color = vbYellow
                Case Else: cel.EntireRow.Interior.Color = vbMagenta
            End Select
        Next
        Application.EnableEvents = True
    End If
    
End Sub

Open in new window

0
Patrick MatthewsCommented:
Here is an example for code when you need >4 conditions, and formulas determine the data in Column C.

The code goes into the "sheet" module for the worksheet you need watched.


Private Sub Worksheet_Calculate()
    
    Dim LastR As Long
    Dim cel As Range
    
    Application.EnableEvents = False
    
    With Me
        LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
        For Each cel In .Range("c2:c" & LastR).Cells
            Select Case UCase(Left(cel, 2))
                Case "": cel.EntireRow.Interior.ColorIndex = xlColorIndexNone
                Case "AA": cel.EntireRow.Interior.Color = vbRed
                Case "BB": cel.EntireRow.Interior.Color = vbBlue
                Case "CC": cel.EntireRow.Interior.Color = vbGreen
                Case "DD": cel.EntireRow.Interior.Color = vbBlack
                Case "EE": cel.EntireRow.Interior.Color = vbYellow
                Case Else: cel.EntireRow.Interior.Color = vbMagenta
            End Select
        Next
    End With
    
    Application.EnableEvents = True
    
End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sq30Author Commented:
Hi I'm going to have initally 7 different criteria which will no doubt increase in time. The values are populated from another piece of code where the original data is from a text file.
0
Patrick MatthewsCommented:
OK then, it should be a simple matter for you to adapt the code above for any number of criteria.
0
sq30Author Commented:
Great I've tested it and it works well however I have a problem that the worksheet is not created until my other code is run also is there a was of only colouring where the data is rather than the whole row e.g in the test file A2:G2.  
0
Patrick MatthewsCommented:
In that case, change:

                Case "": cel.EntireRow.Interior.ColorIndex = xlColorIndexNone

to:

                Case "": Cells(cel.Row, "a").Resize(1, 7).Interior.ColorIndex = xlColorIndexNone

with similar adjustments to the other lines.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sq30Author Commented:
this adjustment appears to stops the code working altogether
0
sq30Author Commented:
Great got it working (I must learn to copy correctly) Thanks for your help. Sq
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.