Use VBA LEFT Function to determine background color in another column

[Jaye Cavallo]

Would someone be able to assist:

In an Excel sheet, I would like to color code a range of cells in column A, red or yellow, based on the string of text in column H. What I am seeking is to use the LEFT function in column H in VBA to determine is column A is red or yellow, based upon the first 3 characters in column H.

 

[6StringJazzer]

What are the specifics about what string tells you to use red vs. what string tells you to use yellow?

This could be done with conditional formatting. Is there any reason you need to use VBA?

 

[Jaye Cavallo]

I have the conditional formatting method in use already. With this particular workbook, I share this with other users who are constantly cutting and pasting (from Excel and external sources) which renders the conditional formatting useless, because I have to continuously fix the conditional formatting. I take advantage of the VBA because it does its work behind the scenes and the user never sees the code. I do include code to delete the non-VBA conditional formatting if a user were to paste something from an Excel document that has conditional formatting before applying the VBA conditional formatting.

 

[6StringJazzer]

Your description is pretty light on details. Put this code in the module for the worksheet with the data. Every time a value in column H is updated it will check the first three characters of the new value. If the first three are "abc" then the corresponding cell in column A will be colored red. Otherwise it will be colored yellow.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Cell As Range
   
   For Each Cell In Target
   
      If Cell.Column = 8 Then ' H
      
         If Left(Cell, 3) = "abc" Then
            Cells(Cell.Row, "A").Interior.Color = RGB(255, 0, 0) ' red
         Else
            Cells(Cell.Row, "A").Interior.Color = RGB(255, 255, 0) ' yellow
         End If
      
      End If
      
   Next Cell
      

End Sub

 

[Jaye Cavallo]

Thank you. Sorry, I forgot to mention that I have a header row. How would I start with Row 2?

 

[bebo021999]

Below code triggers change in column H, from row 2.
It uses Lcase for non- case sensitive , like "abc", "Abc" or "ABC". Remove it if it was case-sensitive.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range, r As String, y As String
r = "abc" ' change "abc" to actual 3 characters of red
y = "def" ' change "def" to actual 3 characters of yellow
If Intersect(Target, Columns("H")) Is Nothing Or Target.Row = 1 Or Target.Count > 1 Then Exit Sub
With Target.Offset(, -7).Interior
    .Color = xlNone
    Select Case LCase(Left(Target, 3))
        Case r
            .Color = RGB(255, 0, 0) ' red
        Case y
            .Color = RGB(255, 255, 0) ' yellow
    End Select
End With
End Sub

 

[Peter_SSs]

Could there be multiple cells in column H changed/entered/pasted at the same time?

 

[Jaye Cavallo]

Thanks, bebo021999, but I get a variable not defined error when this code attempts to execute.

 

[bebo021999]

Thanks, bebo021999, but I get a variable not defined error when this code attempts to execute.

Are there any text string in column H with less than 3 characters?
Could you attach a screenshot/XL2BB to see what data in column H look like?

 

[Peter_SSs]

I get a variable not defined error when this code attempts to execute.

That does not happen for me. Did you modify the code in any way? Which variable is highlighted when you get that message?

BTW, what is the answer to my question in post #7?