Conditional formatting based on another column not containing a specific word (Excel)

Andreamary
Andreamary used Ask the Experts™
on
I would like to implement the following in my Excel spreadsheet:

If Column A does not contain the text string 'internal', then Column B cell changes to green.

The Column A dropdown menu is as follows:
EDX-QA (STiR created)
NC-EQV
NC-FQV
NC-Internal
EDO
NC-NQV
GA QA
UJA-QA (STiR created)

Thanks!
Andrea
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Hi,

You can use below formula in Conditional Formatting selecting column B range:
=ISNUMBER(SEARCH("Internal",A2))=False

Open in new window

See image below:
Conditional Formatting
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Alternatively you can try the below formula for making a new rule for conditional formatting.

=ISERROR(SEARCH("Internal",A2))

Open in new window

ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
If you have long list and you want VBA to do that then try below code:
Sub CondFormat()
Dim Ws As Worksheet
Dim LR As Long
Dim ColRng As Range
Application.ScreenUpdating = False
Set Ws = ActiveSheet
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Set ColRng = Ws.Range("B2:B" & LR)
With Ws
    .Cells.FormatConditions.Delete
    With ColRng
        .FormatConditions.Add Type:=xlExpression, Formula1:="=ISNUMBER(SEARCH(""Internal"",RC[-1]))=FALSE"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(0, 128, 0)
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
End With
Application.ScreenUpdating = True
End Sub

Open in new window

Conditional-Formatting-Based-on-Ajd.xlsm

Author

Commented:
Thanks to you both for your timely solutions! And thanks, Shums, for the taking the extra step of providing a VBA option.

Cheers,
Andrea
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
You're Welcome Andrea! We're glad that we're able to help you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial