CODEWITHSUNDEEP
excelvba
JeffCh
JeffCh

Reputation: 95

Highlight cell in a column if it does not contain specific value/text

I am still new to Macro and would like to get the following code running flawlessly with following conditions:

1) This vba to run from Column E2 until the last entry of the cell, i.e. Vba only runs on cells that have values/entries, do not run on blank cells.

2) Highlight the cell in yellow if that particular cell contains other word.

Below is the code I attempted:

'Highlight cell in Column E if it does not contain "USD"
    If WorksheetFunction.IsText(Range("E2:E").Value) Then
    With Range("E2:E")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Operator:=xlNotEqual, _
            Formula1:="=E2:E<>""USD"""
        With .FormatConditions(1).Interior
            .Color = vbYellow
        End With
    End With
    End If

Issues faced:

1) The first condition is not met.

2) I can only get the second condition worked if I add the specific cell range, for example:

'Highlight cell in Column E if it does not contain "USD"
    If WorksheetFunction.IsText(Range("E2:E").Value) Then
    With Range("E2:**E100**")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Operator:=xlNotEqual, _
            Formula1:="=E2:**E100**<>""USD"""
        With .FormatConditions(1).Interior
            .Color = vbYellow
        End With
    End With
    End If

Kindly need help with this. Thanks.

Upvotes: 0

Views: 1146

Answers (1)

Vitaliy Prushak
Vitaliy Prushak

Reputation: 1162

It's simpler to loop through range and compare cell's text to you condition:

Sub CheckCellAndHighlight()
Dim CheckCell As range, CheckRange As range

Set CheckRange = range(Cells(2, 5), Cells(Rows.Count, 5).End(xlUp)) ' Set the range you need to look through

For Each CheckCell In CheckRange
    If Not UCase(CheckCell.Text) = "USD" Then ' do not need to check whether the cell's value is text, just check whether it meets you particular condition
        CheckCell.Interior.Color = vbYellow ' color it if it meets the condition
    Else
        CheckCell.Interior.Color = xlNone
    End If
Next

End Sub

The Not CheckCell.Text = "USD" means the same as CheckCell.Text <> "USD".

UPDATE

As per comments, added two checks - one for checking the text's case because "usd" is not equal to "USD", and second - clear the color because the cell may have been colored previously.

Upvotes: 2

Related Questions