Highlight cell based on other cell criteria

[Chaozfate]

Hi, I know this can be done in conditional formatting, but I would like to solve this with VBA. The cells will be continuously expanding by copy and paste the existing cell, the conditional formatting formula will follow to increase an cause the excel to overload.

I would like something as following:

Highlight cell A2 in blue if cell B2 detect a criteria with wild card.
Says
A2 will be highlighted if B2 is "*fund in*"
A6 will be highlighted if B6 is "*fund in*"

Would be appreciate if anyone could help on this.

Thanks and regards,
CCU

 

[bebo021999]

Apply this code for first time run (with text string in column B has exists):
Put it in workbook module (Alt-F11, click on "ThisWorkbook", paste below code):

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim lr&, f, ad As String
lr = Sh.Cells(Rows.Count, "B").End(xlUp).Row
Sh.Range("A2:A100000").Interior.Color = xlNone
Set f = Sh.Range("B2:B" & lr).Find("fund in", LookAt:=xlPart, searchorder:=xlByRows)
If Not f Is Nothing Then
    f.Offset(0, -1).Interior.Color = vbYellow
    ad = f.Address
    Do
        Set f = Sh.Range("B2:B" & lr).FindNext(f)
        If Not f Is Nothing Then f.Offset(0, -1).Interior.Color = vbYellow
    Loop Until f.Address = ad
End If
Set f = Nothing
End Sub

For next run, below code fires any change in column B :
Put it in specific worksheet module (Right click on tab name, View code, paste below code)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, f, ad As String
If Intersect(Target, Columns(2)) Is Nothing Then Exit Sub
lr = Cells(Rows.Count, "B").End(xlUp).Row
Range("A2:A100000").Interior.Color = xlNone
Set f = Range("B2:B" & lr).Find("fund in", LookAt:=xlPart, searchorder:=xlByRows)
If Not f Is Nothing Then
    f.Offset(0, -1).Interior.Color = vbYellow
    ad = f.Address
    Do
        Set f = Range("B2:B" & lr).FindNext(f)
        If Not f Is Nothing Then f.Offset(0, -1).Interior.Color = vbGreen
    Loop Until f.Address = ad
End If
Set f = Nothing
End Sub

If you do not want the first code (sheet activate), use the second, but for 1st run, F2 (activate) any cell in column B then Enter

 

[Chaozfate]

thank you so much for the drafted code, however in column A, there is other cells with different color, by applying the above code, the vba overwrite the other cells color too.

wonder if it is possible to eliminate that.

Apologies for the inconvenience caused.

 

[bebo021999]

If you understand how the code work, its easy to twist:
In fifth lines:

Range("A2:A100000").Interior.Color = xlNone

just remove it.

 

[Peter_SSs]

Assuming that it is the wildcard (*) that you are interested in, not just "fund in" and the 'wildcard rows' are to be coloured vbCyan and your other colour(s) are different then try this Worksheet_Change event code

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.ScreenUpdating = False
  With Range("A1", Range("B" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:=vbCyan, Operator:=xlFilterCellColor
    .Columns(1).SpecialCells(xlVisible).Interior.Color = xlNone
    .AutoFilter Field:=1
    .AutoFilter Field:=2, Criteria1:="=*~**"
    With .Columns(1)
      If .SpecialCells(xlVisible).Count > 1 Then .SpecialCells(xlVisible).Interior.Color = vbCyan
      .Cells(1).Interior.Color = xlNone
    End With
    .AutoFilter Field:=2
  End With
  Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

just remove it.

In that case a row that originally contained "fund in" and now doesn't, would still retain its colour wouldn't it?