Question

VBA

Trying to have code search through column containing phrase BEN, then FH, then MH, and color cells with those phrases depending on which phrase is in the cell. I tried it two different ways and neither worked

For r = 2 TO 85 If InStr (cell.Value, BEN) Then Cells(r, 2).Interior. Color = vbRed ElseIf InStr (cell.Value, FH) Then Cells (r, 2). Interior. Color = vbBlue ElseIf Cells (r, 2) = MH Then Cells(r, 2). Interior. Color = vbYellow End If Next r

Answer 1

Steps to implement the Macro:

Select the table in which you want to use the conditional formatting.

Then Click the Alt + F11. The below page will open.

Microsoft Visual Basic - Book2.xlsx - [Sheetl (Code) Type a question for help File Edit View Insert Format Debug Run Iools Add-Ins Window Help Ln 2, Col 1 Properties- Sheetl X SelectionChange Worksheet Sheet1 Worksheet Private Sub Worksheet SelectionChange (ByVal Target As Range) Alphabetic Categorized (Name) DisplayPageBreak False DisplayRightToLef False EnableAutoFilter False EnableCalculation True End Sub Sheet1 EnableFormatCon True EnableOutliningFalse EnablePivotTable False EnableSelection 0-xINoRestricti Name Sheet1 ScrollArea StandardWidth Visible 8.43 -1 - xlSheetVisib

In the page you have to select worksheet and selection range and paste the below code.

VBA Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim My_Range As Range

Set My_Range = Worksheets("Sheet1").Range("B2:B85")

For Each Cell In My_Range

If InStr(Cell.Value, "BEN") Then
Cell.Interior.Color = vbRed
ElseIf InStr(Cell.Value, "FH") Then
Cell.Interior.Color = vbBlue
ElseIf InStr(Cell.Value, "MH") Then
Cell.Interior.Color = vbYellow

End If

Next
End Sub

Explanation

In Dim My_Range As Range we create a new variable named My_Range for specifying the cell range in which we want to apply the cell range in which we want to apply the rules of formatting.

Set My_Range = Worksheets("Sheet1").Range("B2:B85") define the cell range from B2 to B85. Within the range we are going to apply the rules hence we have used the for loop for that. Cell.Value define the value of the specific cell and Interior.color mention the color in which we want to apply on the specific cell. Instead of that we can also use the Cell.Interior.ColorIndex = 1 to mention the color based on the value of the right hand side.