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.

 

[Jaye Cavallo]

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

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?

While removing all other code in the worksheet_Change event, you code does not produce the variable not defined error. However, nothing seems to happens when the change event runs.

These are the values in Column H:

Gas-New Construction

Gas-Paving

Gas-Direct Mail

Electric- 69kV (Pocket)

Electric-Underground

Electric-Reliability (Pocket)

Electric-RPV

Electric-MTB

Electric-Direct Mail

 

[bebo021999]

Have you changed r ="Gas" and y = "Ele" in code?
What the current updated code look like?

 

[Jaye Cavallo]

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?

Sorry, yes it is possible that there could be multiple cells in column H changed/entered/pasted at the same time.

 

[Peter_SSs]

yes it is possible that there could be multiple cells in column H changed/entered/pasted at the same time.

In that case give this a try as your worksheet change event code.
I have taken a guess at red of "gas" and yellow for "ele". Adjust accordingly.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("H"))
  If Not Changed Is Nothing Then
    For Each c In Changed
      With Range("A" & c.Row)
        Select Case UCase(Left(c.Value, 3))
          Case "GAS": .Interior.Color = vbRed
          Case "ELE": .Interior.Color = vbYellow
          Case Else: .Interior.Color = xlNone
        End Select
      End With
    Next c
  End If
End Sub

 

[Jaye Cavallo]

In that case give this a try as your worksheet change event code.
I have taken a guess at red of "gas" and yellow for "ele". Adjust accordingly.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
 
  Set Changed = Intersect(Target, Columns("H"))
  If Not Changed Is Nothing Then
    For Each c In Changed
      With Range("A" & c.Row)
        Select Case UCase(Left(c.Value, 3))
          Case "GAS": .Interior.Color = vbRed
          Case "ELE": .Interior.Color = vbYellow
          Case Else: .Interior.Color = xlNone
        End Select
      End With
    Next c
  End If
End Sub

I copied your code into a new, blank workbook. Created some content in column H (Gas, Electric, Gas, Electric, etc..). Nothing happens to column A.

 

[Peter_SSs]

I copied your code into a new, blank workbook.

Where, exactly? It needs to go into the module of the relevant worksheet. For me, that is in the Sheet1 module as shown.

 

[Jaye Cavallo]

Where, exactly? It needs to go into the module of the relevant worksheet. For me, that is in the Sheet1 module as shown.

View attachment 74544

Yeah, that is where I placed the code.

 

[Peter_SSs]

Then can you upload that sample file to DropBox or OneDrive or Google drive etc and provide a shared link here so we can take a look to investigate?

 

[Jaye Cavallo]

Then can you upload that sample file to DropBox or OneDrive or Google drive etc and provide a shared link here so we can take a look to investigate?

Try this link. I created a sample file. schedule_test_sample.xlsm

 

[Peter_SSs]

Thanks for the sample file. The code that I provided is working fine. However, you have combined it with other code that you didn't tell us about and that code is removing the colour that my code applied.

Try making this change in your code.

WS.Range("A2:z3569").Cells.Interior.ColorIndex = xlNone
WS.Range("B2:z3569").Cells.Interior.ColorIndex = xlNone

For any values already in column H you will need to re-enter them. For example, you could Copy -> Paste values in column H all at once